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
I had iTunes on my old computer and the programming was messed up and I lost my itunes. I now have a new computer and I want to restore all the music I previously bought, as it was quite a lotIf for some reason you have failed to maintain a backup co [More]
For the past six months I have been following a manual process to convert JPG files (exported from InDesign) into SWF files for display advertising assets.  Using pre-built FLA files as templates (configured with clicktags): Open FLA templates (3 sep [More]
Hi gurus: Please help me figure out why even though I have activated the purchase document field in the field status group I still cannot see it in the line item reports when I run FBL3n. Note the purchase document field is showing up as a field for [More]
...all other things being equal. Which file type is better and why? Thanks!dastoelk wrote:...  and why? mp3 could be made with a VBR - and that, on the long run, could cause synch-problems. Or, mp3 often use a different sample-rate than the mentioned [More]
Hi friends, Could you please help me out with master table for Qualification ID and its description? Thanks, DebasishHRP1000Read other 2 answers [More]
Hi, I've seen a number of threads on here about the above message, but most are answered/archived, so no replies are allowed. I managed to set up the Mac Mini server and get a VPN up and running quite successfully, however the upload speed was incred [More]
Received a manual from a supplier in PDF format. Left and right 1/2 inch of document does not print. Tried letter & legal paper, Adobe help and zooming. danm5543 wrote: Could you please explain what might be accomplished by "upload that PDF docum [More]
Cannot download trial version. This always appears "You are running an operating system that After Effects no longer supports. Refer to the system requirements below for a full list of supported platforms." PLEASE HELP.Hello, in this case please [More]
downloaded photoshop elements 13 with windows 8.1. cant find itrestart the download and note where you're storing the file. Downloads available: Suites and Programs:  CC 2014 | CC | CS6 | CS5.5 | CS5 | CS4 | CS3 Acrobat:  XI, X | 9,8 | 9 standard Pre [More]
My 1st ipod click wheel 40 Gb ipod gave up the ghost after 9 months and decided to have its own electrical fire (wonderful). After 3 months of trying to get a replacement, the replacement won't work. I have downloaded the software, charged the new ip [More]