[Home] [Help]
81: cur_get_man_ar_inv_taxes for performance issue
82: reported.
83: It increased the performance
84: as these columns have index defined on them in
85: jai_rgm_trx_records.
86:
87: 8. 02-Apr-2010 Allen Yang modified for bug 9485355 (12.1.3 non-shippable Enhancement)
88: added parameter p_order_line_id in procedure process_order_invoice and modified
89: procedure process_order_invoice to process non-shippable lines.
92: 9. 2010/04/14 Bo Li For bug9305067
93: The procedure jai_cmn_rgm_recording_pkg.insert_vat_repository_entry has been called
94: in the package JAI_CMN_VAT_ACCNT_PKG. Because of the change of the jai_cmn_rgm_recording_pkg.insert_vat_repository_entry,
95: the package JAI_CMN_VAT_ACCNT_PKG should be modified.
96: The attribute column of JAI_RGM_TRX_RECORDS are also used. To follow the development standard, they should be
97: replaced by new columns with new meaningful ones
98:
99: 10. 20-Apr-2010 Allen Yang modified for bug 9602968
100: Modified procedure definition of process_order_invoice added 'DEFAULT NULL' for p_order_line_id
100: Modified procedure definition of process_order_invoice added 'DEFAULT NULL' for p_order_line_id
101: Version 120.11.12010000.4
102:
103: 11. 09-JUN-2010 Bo Li modified for bug#9766552
104: Issue - Account_name column in table jai_rgm_trx_records is null for VAT tax of
105: non-shippable RMA.
106: Fix - Give the account name "RECOVERY" for the transaction type "Credit Memo"
107:
108: 12. 25-Feb-2011 Added by Zhiwei for Open Interface ER bug#11683927
185: add 'DEFAULT NULL' for parameter p_order_line_id.
186: ***************************************************************************************************/
187: AS
188:
189: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
190: ln_liab_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
191: ln_intliab_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
192: ln_charge_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
193: ln_balancing_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
193: ln_balancing_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
194:
195: lv_process_flag VARCHAR2(2) ;
196: lv_process_message VARCHAR2(1996) ;
197: ln_debit_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
198: ln_credit_amount JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE ;
199:
200: --Date 14/06/2007 by sacsethi for bug 6072461 (for VAT Reversal)
201: ln_recov_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
194:
195: lv_process_flag VARCHAR2(2) ;
196: lv_process_message VARCHAR2(1996) ;
197: ln_debit_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
198: ln_credit_amount JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE ;
199:
200: --Date 14/06/2007 by sacsethi for bug 6072461 (for VAT Reversal)
201: ln_recov_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
202: ln_expense_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
206: --start
207: CURSOR c_chk_rgm_trxs(cp_transaction_header_id in number,cp_transaction_temp_id in number,cp_tax_id in number) IS
208: SELECT 1
209: FROM
210: jai_rgm_trx_records jrtr
211: WHERE
212: jrtr.trx_reference1 = cp_transaction_header_id AND -- Modifiied By Bo Li for replaceing the attribute1 with trx_reference1
213: jrtr.source_document_id = cp_transaction_temp_id AND
214: jrtr.reference_id = cp_tax_id AND
258: * Included source, source_trx_type and source_table_name in the inner
259: * query for performance issue reported.
260: */
261: CURSOR cur_get_deliveries
262: ( cp_source IN jai_rgm_trx_records.source%TYPE,
263: cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
264: cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE
265: )
266: IS
259: * query for performance issue reported.
260: */
261: CURSOR cur_get_deliveries
262: ( cp_source IN jai_rgm_trx_records.source%TYPE,
263: cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
264: cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE
265: )
266: IS
267: SELECT /*+ ordered use_concat */ -- for bug 16058504 by anupgupt
260: */
261: CURSOR cur_get_deliveries
262: ( cp_source IN jai_rgm_trx_records.source%TYPE,
263: cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
264: cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE
265: )
266: IS
267: SELECT /*+ ordered use_concat */ -- for bug 16058504 by anupgupt
268: jspl.delivery_id ,
301: -- jrttv.regime_code = jai_constants.vat_regime AND --Date 14/06/2007 by sacsethi for bug 6072461
302: NOT EXISTS
303: -- start for bug 16058504 by anupgupt
304: (SELECT 1
305: FROM jai_rgm_trx_records jrtr
306: WHERE jrtr.source = cp_source
307: AND jrtr.source_trx_type = cp_source_trx_type
308: AND jrtr.organization_id = p_organization_id
309: AND jrtr.location_id = p_location_id
312: AND jrtr.source_document_id = jspl.delivery_detail_id
313: AND jrtr.reference_id = jsptl.tax_id
314: UNION ALL
315: SELECT 1
316: FROM jai_rgm_trx_records jrtr
317: WHERE jrtr.source = cp_source
318: AND jrtr.source_trx_type = cp_source_trx_type
319: AND jrtr.organization_id = p_organization_id
320: AND jrtr.location_id = p_location_id
323: AND jrtr.source_document_id = jspl.order_line_id
324: AND jrtr.reference_id = jsptl.tax_id
325: )
326: /* ( SELECT 1
327: FROM jai_rgm_trx_records jrtr
328: WHERE
329: -- Bug 5739005. Added by vkantamn
330: jrtr.source = cp_source AND
331: jrtr.source_trx_type = cp_source_trx_type AND
390: * Included source, source_trx_type and source_table_name in the inner
391: * query for performance issue reported.
392: */
393: CURSOR cur_get_man_ar_inv_taxes
394: ( cp_source IN jai_rgm_trx_records.source%TYPE,
395: cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
396: cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE
397: )
398: IS
391: * query for performance issue reported.
392: */
393: CURSOR cur_get_man_ar_inv_taxes
394: ( cp_source IN jai_rgm_trx_records.source%TYPE,
395: cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
396: cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE
397: )
398: IS
399: SELECT
392: */
393: CURSOR cur_get_man_ar_inv_taxes
394: ( cp_source IN jai_rgm_trx_records.source%TYPE,
395: cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
396: cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE
397: )
398: IS
399: SELECT
400: jctl.customer_trx_id ,
427: jtc.tax_type = jrttv.tax_type AND
428: NOT EXISTS ( SELECT
429: 1
430: FROM
431: jai_rgm_trx_records jrtr
432: WHERE
433: -- Bug 5739005. Added by vkantamn
434: jrtr.source = cp_source AND
435: jrtr.source_trx_type = cp_source_trx_type AND
443: ) ;
444:
445:
446: /* Following variables added for Projects Billing. Bug# 6012570 (5876390) */
447: lv_source_trx_type jai_rgm_trx_records.source_trx_type%type;
448: lv_source_table_name VARCHAR2(30);
449: lv_called_from VARCHAR2(100); /*ssumaith - changed the width to 100 from 30 */
450: lv_attribute_context VARCHAR2(30);
451: ln_source_id NUMBER(15);