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 am new to Java and am trying to learn how to use and understand the nuances involved in using the Switch statment. Yesterday, I received tremendous help, As a result, I am closer to understanding the switch statement and how it works. My program is [More]
Hi all, I am facing a problem while sending multiple files, I need to send files ( File1, File2 or FIle3) ... I was able to successfully send the first 2 files and along with it the 3rd file is being created without any data... How to stop this file( [More]
Hi,  When using LR3 to create DNG files from my RAW files, those files would show me thumbnails of my image in Windows Explorer.  I am running Windows/7 (64) and have installed a CODEC from "Fast Picture Viewer" that allows thumbnails from RAW & [More]
Is it possible to pxe boot to both known and unknown computers in a domain? I work in a school environment and we have been using WDS to deploy our images. I recently was able to get SCCM up and running in our environment, and created a working TS to [More]
Hi, I'm a newbie to Scripting in Indesign. Can you name me wich properties I have to change (and the values) for the double and single left quotation marks to set the strait one's. CS or CS3 please - VB or JS is ok. Thankx FrankHi Frank 1. To change [More]
I have created a button with a text field. This is an internal linked object. I was hoping to be able to edit the text for each instance of the button. But still be able to later change the look. Is there a way to preserve the local text edits? Thank [More]
Hi All, You might found this question silly but I don't know so asking this question here. I have cold back up of the database. Now, I want create clone of that database, but I have some different paths for the DBFs so I will create new control file [More]
Ever since I downloaded trial version Adobe after effects through Creative Cloud, all my other other licensed products like Photoshop, Captivate, Flash crashes everytime I try to open them. Every day I have to install them. Its really frustrating. Th [More]
I followed the directions on the apple support-the music all moved, but my playlists were missing- Anyone have any ideas on this? I'm moving music from windows XP to an Imac with OS 10.5.3 ThanksHmm-now that I look at the imac files, it seems that th [More]
Hello, good day.      Currently I have a Mac with Adobe Creative Suite 6 Design Standard, however, the life of my mac came to an end and I want to know the possibility to migrate the license to a computer with Windows OS.     Is it possible to instal [More]