Sitemap

Amicuk Programming Answers

PLSQL compiles but doesn't run.. I've declared it everywhere but still..

2015-10-11   Views:1

Advertisement

PLSQL compiles but doesn’t run.. I’ve declared it everywhere but still.. Afternoon.. Hopefully a quick one for someone.. I’m trying to run a Concurrent Program in ORACLE Financials using a Data Template derived BI Publisher report. Error message rece

PLSQL compiles but doesn’t run.. I’ve declared it everywhere but still..
Afternoon.. Hopefully a quick one for someone.. I’m trying to run a Concurrent Program in ORACLE Financials using a Data Template derived BI Publisher report.
Error message received..
SUBIXCLT module: UofS Expense Claim Tracking Report
+--------------------------------------------------------------------------
All Parameters: raisedby=:status=:claimant=:expense_date_from=:expense_date_to=:LP_ORDERED_BY=Expense Report Number
Data Template Code: SUBIXCLT
Data Template Application Short Name: PO
Debug Flag: N
{raisedby=, claimant=, expense_date_to=, expense_date_from=, status=, LP_ORDERED_BY=Expense Report Number}
Calling XDO Data Engine...
[060410_025628319][][STATEMENT] Start process Data
[060410_025628324][][STATEMENT] Process Data ...
[060410_025628329][][STATEMENT] Executing data triggers...
[060410_025628329][][STATEMENT] BEGIN
SUBIXCLT.claimant := :claimant ;
SUBIXCLT.expense_date_from := :expense_date_from ;
SUBIXCLT.expense_date_to := :expense_date_to ;
SUBIXCLT.raisedby := :raisedby ;
SUBIXCLT.status := :status ;
SUBIXCLT.lp_ordered_by := :lp_ordered_by ;
:XDO_OUT_PARAMETER := 1;
END;
l_flag Boolean;
BEGIN
l_flag := SUBIXCLT.BEFOREREPORT(L_ORDERED) ;
if (l_flag) then
:XDO_OUT_PARAMETER := 1;
end if;
end;
[060410_025628356][][EXCEPTION] SQLException encounter while executing data trigger....
java.sql.SQLException: ORA-06550: line 4, column 33:
PLS-00201: identifier 'L_ORDERED' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignoredThe Data Template
The Data Template
<?xml version="1.0" encoding="utf-8" ?>
- <dataTemplate name="UofS_OutstandngExpenses_Report" defaultPackage="SUBIXCLT" dataSourceRef="FINDEV" version="1.0">
- <properties>
  <property name="xml_tag_case" value="upper" />
  <property name="include_parameters" value="true" />
  <property name="debug_mode" value="on" />
  </properties>
- <parameters>
  <parameter name="claimant" dataType="character" defaultValue="" />
  <parameter name="expense_date_from" dataType="date" defaultValue="" />
  <parameter name="expense_date_to" dataType="date" defaultValue="" />
  <parameter name="raisedby" dataType="character" defaultValue="" />
  <parameter name="status" dataType="character" defaultValue="" />
  <parameter name="lp_ordered_by" dataType="character" defaultValue="" />
  </parameters>
- <dataQuery>
  <dataTrigger name="beforeReportTrigger" source="SUBIXCLT.BEFOREREPORT(L_ORDERED)" />
- <sqlStatement name="Q1">
- <![CDATA[
SELECT DISTINCT
erh.invoice_num,
pap.full_name EMP_CLAIMING,
DECODE(NVL(erh.expense_status_code, 'Not yet Submitted (NULL)'), 'CANCELLED', 'CANCELLED',
     'EMPAPPR', 'Pending Individuals Approval',      'ERROR', 'Pending System Administrator Action',
     'HOLD_PENDING_RECEIPTS     ', 'Hold Pending Receipts', 'INPROGRESS', 'In Progress', 'INVOICED', 'Ready for Payment',
     'MGRAPPR', 'Pending Payables Approval', 'MGRPAYAPPR', 'Ready for Invoicing', 'PAID', 'Paid',
     'PARPAID', 'Partially Paid',     'PAYAPPR', 'Payables Approved',     'PENDMGR', 'Pending Manager Approval',
     'PEND_HOLDS_CLEARANCE', 'Pending Payment Verification',     'REJECTED', 'Rejected',     'RESOLUTN',     'Pending Your Resolution',
     'RETURNED',     'Returned',     'SAVED',     'Saved',     'SUBMITTED',     'Submitted',     'UNUSED',     'UNUSED',
     'WITHDRAWN','Withdrawn',     'Not yet Submitted (NULL)') "EXPENSE_STATUS" ,
NVL(TO_CHAR(erh.report_submitted_date,'dd-MON-yyyy'),'NULL') SUBMIT_DATE,
NVL(TO_CHAR(erh.expense_last_status_date,'dd-MON-yyyy'),'NULL') LAST_UPDATE,
erh.override_approver_name ER_Approver,
fu.description EXP_ADMIN,
erh.total,
erh.description 
FROM
AP_EXPENSE_REPORT_HEADERS_all erh,
per_all_people_f pap, fnd_user fu
WHERE erh.employee_id = pap.person_id
AND fu.user_id = erh.created_by
AND NVL(erh.expense_status_code, 'Not yet Submitted') NOT IN  ('MGRAPPR', 'INVOICED', 'PAID', 'PARPAID')
AND pap.full_name = NVL(:claimant, pap.full_name)
AND TRUNC(erh.report_submitted_date) BETWEEN NVL(:expense_date_from, '01-JAN-1999') AND NVL(:expense_date_to,'31-DEC-2299')
AND fu.description = NVL(:raisedby,fu.description)
AND erh.expense_status_code = NVL(:status,erh.expense_status_code) &LP_ORDERED_BY
  ]]>
  </sqlStatement>
  </dataQuery>
  <dataTrigger name="beforeReportTrigger" source="SUBIXCLT.BEFOREREPORT(L_ORDERED)" />
- <dataStructure>
- <group name="G_XP_CLM_TRACKNG" source="Q1">
  <element name="INVOICE_NUM" value="INVOICE_NUM" />
  <element name="EMP_CLAIMING" value="EMP_CLAIMING" />
  <element name="EXPENSE_STATUS" value="EXPENSE_STATUS" />
  <element name="SUBMIT_DATE" value="SUBMIT_DATE" />
  <element name="LAST_UPDATE" value="LAST_UPDATE" />
  <element name="LP_ORDERED_BY" dataType="varchar2" value="SUBIXCLT.LP_ORDERED_BY" />
  </group>
  </dataStructure>
  </dataTemplate>The PL SQL..
The PL SQL..
CREATE OR REPLACE PACKAGE Subixclt IS
L_ORDERED  VARCHAR2(50);
RAISEDBY VARCHAR2(50);
STATUS VARCHAR2(50);
CLAIMANT VARCHAR2(50);
LP_ORDERED_BY VARCHAR2(50);
FUNCTION BEFOREREPORT(L_ORDERED IN VARCHAR2) RETURN VARCHAR2;
EXPENSE_DATE_FROM DATE;
EXPENSE_DATE_TO DATE;
--RETURN VARCHAR2;
END;
CREATE OR REPLACE PACKAGE BODY Subixclt IS
FUNCTION BEFOREREPORT(L_ORDERED IN VARCHAR2)RETURN VARCHAR2 IS
BEGIN
Fnd_File.PUT_LINE(Fnd_File.LOG,'L_ORDERED'||L_ORDERED);
DECLARE
LP_ORDERED_BY VARCHAR2(50);
L_ORDERED  VARCHAR2(50);
RAISEDBY VARCHAR2(50);
STATUS VARCHAR2(50);
CLAIMANT VARCHAR2(100);
EXPENSE_DATE_FROM DATE;
EXPENSE_DATE_TO DATE;
BEGIN
IF (LP_ORDERED_BY='Expense Report Number') THEN
     LP_ORDERED_BY :='order by 1 asc;';
  ELSIF (LP_ORDERED_BY='Person Claiming') THEN
     LP_ORDERED_BY :='order by 2 asc;';
  ELSIF (LP_ORDERED_BY='Submit Date') THEN
  LP_ORDERED_BY :='order by 4 asc;';
  END IF;
RETURN(L_ORDERED);
--RETURN NULL;
END;
END;
END;Thanks for looking..
Steven
Edited by: Mr_Alkan on Jun 4, 2010 3:35 PM

The replay answer
Advertisement
One has to initialise a session first for use with Oracle Apps if you want to make it run as a concurrent job.
Any decleration within your package will not be recognised unless initialisation is sucessful.
Investigate the built-in packages:
FND_GLOBAL - for initialisation
FND_SUBMIT - for setting session relevant parameters
-- function returns true or false depending on whether the initialisation was sucessful or not
create or replace function is_Init_OK (p_User_Name       in varchar2
                                      ,p_Responsibility  in varchar2
                                      ,p_Language        in varchar2) return boolean as
  b_Set_NLS   boolean;
  b_Set_Mode  boolean;
  r_ISet      fnd_Init := Get_Init_Set(p_User_Name, p_Responsibility);
  begin
    -- 1
    fnd_global.apps_initialize(r_ISet.User_ID, r_ISet.Resp_ID, r_ISet.App_ID);
    -- 2
    b_Set_NLS := fnd_submit.set_nls_options(p_Language);
    -- 3
    b_Set_Mode  := fnd_submit.set_mode (false);
    return (b_Set_Mode and b_Set_NLS and (    (r_ISet.Resp_ID is not null)
                                          and (r_ISet.User_ID is not null)
    exception
      when others then
        return false;
end is_Init_OK;
-- for example
declare
  l_User_ID number = 'IMPORT_POST'; --- import post user
  l_Resp    number =  'Import and Posting responsibility' -- import posting responsibility
  l_Language varchar2(100) := 'AMERICAN';
  b_Init boolean := false;
  INIT_EXCEPTION exception;
begin
  b_Init := is_Init_OK(l_User_ID, l_Resp, l_Language);
  if (not b_Init) then
    raise INIT_EXCEPTION;
  end if;
  -- conitnue with your processing
  exception 
    when others then
      when INIT_EXECPTION then
      when others then
end;
/

Go to See the other 5 answers

PLSQL compiles but doesn't run.. I've declared it everywhere but still..

Category:DefaultRelease time:2015-10-11Views:130

PLSQL compiles but doesn’t run.. I’ve declared it everywhere but still.. Afternoon.. Hopefully a quick one for someone.. I’m trying to run a Concurrent Program in ORACLE Financials using a Data Template derived BI Publisher report. Error message rece[More]

Apex Native PLSQL Compile?

Category:DefaultRelease time:-0001-11-30Views:130

Hi I am considering changing my databases to native PLSQL compilation.. Reading Joels Blog from 2009.. it was good... but had issues http://joelkallman.blogspot.co.nz/2009/02/make-all-of-your-apex-applications-run.html But that was 2009.. and we now[More]

File Adapter - WinXP at compile time, Unix at run time

Category:DefaultRelease time:2015-10-11Views:130

Regarding File Adapter - WinXP at compile time, Unix at run time I want to specify polling of a directory using Unix type directory notation but this doesn't compile under windows. (I'll read up re. logical directories) Has anyone done polling of a u[More]

Program compiles, but does not run

Category:DefaultRelease time:-0001-11-30Views:130

To: XCode Users <[email protected]> From: Brigit Ananya <[email protected]> Subject: Program compiles, but does not run I am trying to port a Java application from the PC to the Mac. I am using XCode and the program compiles, but it[More]

Can compile but can't run....

Category:DefaultRelease time:-0001-11-30Views:130

Last year I compiled and ran programs and applets with the Java SDK Version 1.4.0 on Windows XP. This year I am basically going thru the same motions, but now I can compile programs but not run them. The error that I get using the DOS prompt command[More]

It compiles but can't run it!

Category:DefaultRelease time:-0001-11-30Views:130

Hi I am really new to java. I got this code from net and tried to compile it. Idoes compile but when i run it , i get the messege, "java.lang.NoSuchMethodError: main Exception in thread "main" . Here is me code.Can someone pleaese tell me y[More]

Forms 9i PLSQL Compiler

Category:DefaultRelease time:-0001-11-30Views:130

Ha1s anyone had any problems upgrading their Forms from 6i to 9i specifically due to PLSQL compiler problems? Presumably they are different versions. Does anyone know the PLSQL compiler version nos in Forms 6i and Forms 9i?I have converted some of my[More]

Stumped with SwingWorker -- code compiles but doesn't run right

Category:DefaultRelease time:-0001-11-30Views:130

Okay, sorry to post a ton of code, but I'm creating a swing gui that will use swing worker to create an object of the followng class, one method that sleeps and returns a random number: public class BigJob{      public int doBigJob(){           try {[More]

Compile-time v/s Run-time

Category:DefaultRelease time:-0001-11-30Views:130

I have an servlet based application that is compiled in JDK 1.1.7 and for running it, my application server (Tomcat) used JRE 1.3.1 (JIT enabled). The question is if I compile my application in JDK 1.3.1 (using a new IDE) would my application perform[More]

Capturing PLSQL compilation error in PLSQL

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I am thinking of compiling a package through another package. If the compilation returns an error, how can i capture that error? I want to grab the error and format them on an email. I am talking about the error that we see in SQL*Plus when we us[More]

Compiling against developer database, running against production database??

Category:DefaultRelease time:-0001-11-30Views:130

Hello :-) In our company we have a developer- , a test- and a production database, like most of us, I think... :-) In former times it was usual to compile forms against the database, that they are running against. But nowadays this is because of new[More]

Hot
Hello Dears. I had encrypted my drive by Bitlocker in windows 7.  few days after my friend did quick format in my encrypted drive. my 300 gb of data lost. and nothing saved i remember unlock password.  how can i recover my data ??? i need my data i t [More]
I purchased CS4 from Adobe a few days ago and followed the instructions to load to a new iMac (OS 10.5 4GB Ram) which is a couple of weeks old. This is the first software I have attempted to load onto it.  After trying twice and getting an error mess [More]
Dear All, How to setup the tolerance limits for a user to enter MIRO invoices to 2% greater than the line amount of a Purchase order and no limit for less than the line amount. Regards, Shashank. Edited by: Shashank on Sep 21, 2010 5:31 PMIMG  MM \ L [More]
Hi All, I have developed a code and the output of the code as to send to a mial id's mentioned on the selection screen with a PDF attachemnt. So can any one suggest me how to do and it won't run in background it should always run in foreground. Any e [More]
Hi, do I have to use rEFIt to boot Linux on my MacBook, or can I just use Bootcamp? Thanks, MrAllan EDIT: In the end, I installed rEFIt, then Linux, and finally uninstalled rEFIt and am now succesfully dual-booting between Linux and Mac OS X using Bo [More]
Hi. Any opinions on whether having my libraries on their own dedicated partition on my internal HDs (iMac and MBPro) would improve performance? FWIW I am using referenced files, which will continue to live on the boot drive, for now. Here's the bonus [More]
I have a xls file that I can read and modify using POI. Users are providing another file in csv format that the program should read and make adjustments to the xls already read in the memory. Can anyone suggest the most efficient way to implement thi [More]
Hi Everyone, I want to add new field for movement type 301(vendor material no)can you please guide me how to accomplish this task. Thanks VenkatDear, Do the 311 movement through the MIGO and then set field for MIGO Vendor Material No.     GOITEM-IDNL [More]
Can I intstall Aperture 3 on my new macbook pro if I initially installed it on my current macbook pro using the trial version and product key?  The trial version of Aperture 3 is no longer offered on Apple's site. Thanks.Can you tell me if there is a [More]
I need to run a query, like select * from <table> where <column1> in (<some 76000 values>); I am not sure, wether there is a limitaion on the number of values we can place in the right of the 'IN' clause (I have some 76000 values to comp [More]