Hello guys
I wonder if you can help me. I have a simple proc that does insert into GL_INTERFACE. I'm doing this by passing a FROM GL_DATE and TO_GL_DATE. The proc runs fine when I execute it from TOAD. I want to run it as a concurrent request in Oracle Apps. My problem is I don't know how to pass my date parameters in Oracle Apps. On the Concurrent Program Executable form,I've defined my Execution Method as 'PL/SQL Stored Procedure'. And the Execution File Name is: IS_GL_TRANSFER. Then on the Concurrent Programs form, I created two parameters for the FROM_DATE and TO_DATE. But when I run the concurrent request, it doesn't insert into the GL_INTERFACE. So I don't think that I'm passing it the date parameters. How do I define the date input parameters in Oracle Apps? Here's my proc. I can execute it from TOAD and sql plus by passing it the dates. I'd like to know how to pass the dates when running it as a concurrent request.
<
CREATE OR REPLACE PROCEDURE APPS.IS_GL_TRANSFER(p_from_date IN VARCHAR2
,p_to_date IN VARCHAR2
,p_errbuf OUT VARCHAR2
,p_retcode OUT VARCHAR2) IS
BEGIN
--insert into GL_INTERFACE
insert into GL_INTERFACE
(status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
date_created,
created_by,
actual_flag,
accounting_date,
code_combination_id,
currency_code,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr)
select 'NEW',
1001,
'Reallocation',
'Reallocation',
SYSDATE,
1844,
'A',
ara.gl_date,
ard.code_combination_id,
'ZAR',--th.invoice_currency_code,
ABS(round( (IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'DR') -
IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'CR') ),2) ) ACCOUNTED_DR,
TO_NUMBER(NULL) ACCOUNTED_CR,
ABS( round( (IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'DR') -
IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'CR') ) ,2) ) ENTERED_DR,
TO_NUMBER(NULL) ENTERED_CR
from APPS.ar_cash_receipts_all rec,
APPS.ar_receivable_applications_all ara,
APPS.ar_distributions_all ard,
APPS.ra_customer_trx_all th,
APPS.ra_customer_trx_lines_all tl,
APPS.ra_cust_trx_line_gl_dist_all ragl,
APPS.gl_code_combinations glcc
where ara.cash_receipt_id = rec.cash_receipt_id
and ard.source_id = ara.receivable_application_id
and ara.applied_customer_trx_id = th.customer_trx_id
and th.customer_trx_id = tl.customer_trx_id
and tl.customer_trx_line_id = ragl.customer_trx_line_id
and ragl.code_combination_id = glcc.code_combination_id
and ard.code_combination_id = 3650
and ard.source_type like 'EXCH%'
and tl.line_type = 'LINE'
and tl.extended_amount <> 0
and ragl.account_class IN ('REV','FREIGHT')
and ragl.acctd_amount <> 0
--and th.trx_number = '768191'
and ara.gl_date between P_FROM_DATE and P_TO_DATE /*TO_DATE('01/11/2008','DD/MM/YYYY') and TO_DATE('30/11/2008','DD/MM/YYYY') */
and SUBSTR(IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'DR') -
IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'CR') ,1,1) LIKE '-%'
UNION ALL
select 'NEW',
1001,
'Reallocation',
'Reallocation',
SYSDATE,
1844,
'A',
ara.gl_date,
ard.code_combination_id,
'ZAR',--th.invoice_currency_code,
TO_NUMBER(NULL),
round( (IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'DR') -
IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'CR') ) ,2) ACCOUNTED_CR,
TO_NUMBER(NULL),
round( (IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'DR') -
IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'CR') ) ,2) ENTERED_CR
from APPS.ar_cash_receipts_all rec,
APPS.ar_receivable_applications_all ara,
APPS.ar_distributions_all ard,
APPS.ra_customer_trx_all th,
APPS.ra_customer_trx_lines_all tl,
APPS.ra_cust_trx_line_gl_dist_all ragl,
APPS.gl_code_combinations glcc
where ara.cash_receipt_id = rec.cash_receipt_id
and ard.source_id = ara.receivable_application_id
and ara.applied_customer_trx_id = th.customer_trx_id
and th.customer_trx_id = tl.customer_trx_id
and tl.customer_trx_line_id = ragl.customer_trx_line_id
and ragl.code_combination_id = glcc.code_combination_id
and ard.code_combination_id = 3650
and ard.source_type like 'EXCH%'
and tl.line_type = 'LINE'
and tl.extended_amount <> 0
and ragl.account_class IN ('REV','FREIGHT')
and ragl.acctd_amount <> 0
--and th.trx_number = '768191'
and ara.gl_date between P_FROM_DATE and P_TO_DATE /*TO_DATE('01/11/2008','DD/MM/YYYY') and TO_DATE('30/11/2008','DD/MM/YYYY') */
and SUBSTR(IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'DR') -
IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'CR') ,1,1) NOT LIKE '-%'
UNION ALL
select 'NEW',
1001,
'Reallocation',
'Reallocation',
SYSDATE,
1844,
'A',
ara.gl_date,
ragl.code_combination_id,
'ZAR',--th.invoice_currency_code,
round( IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'DR'),2) ACCOUNTED_DR,
round( IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'CR'),2) ACCOUNTED_CR,
round( IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'DR'),2) ENTERED_DR,
round( IS_CALC_ALLOCATIONS(th.customer_trx_id,tl.customer_trx_line_id ,ard.line_id ,ragl.cust_trx_line_gl_dist_id,'CR'),2) ENTERED_CR
from APPS.ar_cash_receipts_all rec,
APPS.ar_receivable_applications_all ara,
APPS.ar_distributions_all ard,
APPS.ra_customer_trx_all th,
APPS.ra_customer_trx_lines_all tl,
APPS.ra_cust_trx_line_gl_dist_all ragl,
APPS.gl_code_combinations glcc
where ara.cash_receipt_id = rec.cash_receipt_id
and ard.source_id = ara.receivable_application_id
and ara.applied_customer_trx_id = th.customer_trx_id
and th.customer_trx_id = tl.customer_trx_id
and tl.customer_trx_line_id = ragl.customer_trx_line_id
and ragl.code_combination_id = glcc.code_combination_id
and ard.code_combination_id = 3650
and ard.source_type like 'EXCH%'
and tl.line_type = 'LINE'
and tl.extended_amount <> 0
and ragl.account_class IN ('REV','FREIGHT')
and ragl.acctd_amount <> 0
--and th.trx_number = '768191'
and ara.gl_date between P_FROM_DATE and P_TO_DATE; /*TO_DATE('01/11/2008','DD/MM/YYYY') and TO_DATE('30/11/2008','DD/MM/YYYY');*/
COMMIT;
END IS_GL_TRANSFER;
/
>