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 was trying to install Windows 7 Professional x64 on a brand new 2014 15" Retina Macbook Pro when I got this error. Every time the windows boot gets to the 'Install Windows" section, the first thing that comes up is this error. You can look for [More]
Dear All, I have problem with SQL Server 2008 I want add host name and ip address of client in to audit as default column. very thanks.Dear All, I have working with Audit Specifications in SQL Server 2008. But result cannot have HOST_NAME and IPADDRE [More]
Hello everybody, I am trying to find programatically all the subVI's used in a VI and save them together in the same folder. I did that using Get VI Dependencies Invoke Method. It works with VIs, ctl, llb and lvlib (I can get their names and paths).  [More]
Please, kindly roll out an update to solve the permanent flight mode problem affecting wp8.1 Lumia update. My phone has remained useless since I updated to Lumia cyan. Permanent flight mode rendered my beautiful adorable Lumia 520 useless. Urgent sof [More]
Sorry if this is in the wrong place (newbie here) I'm trying to order an 'Automatic list' in Alphabetical order - based on page name/title So within WPC I have a folder with 10 or so web pages in them. and the next level up I have a page with the 'Dy [More]
Everytime I sync my 3Gs, sync warns me that it cannot locate some music tracks. For example, about 330 songs out of 1500 this morning were missing. So I go through and locate all of the MIA tracks and re-sync. Every thing is ok until after I close iT [More]
As stated in the header, iCloud Mail, Contacts, and Calenders Software Update all want to open in 32 bit mode.  Then when System Preferences quits and relaunches IN 32 bit mode, I get a dialog box saying "You can't open (pick one) preferences because [More]
I understand the diffrence between CMYK and RGB but... My colours look dull on screen in CMYK - what if I want to send something to a client? I can't send a PDF off for approval with all dull looking colours even if the final print will look brighter [More]
The icons on my 2011 Macbook Pro 17" just started to change their size on their own. I first thought that I've pressed a shortcut or something but sometimes the icons will just go to maximum or minimum size on their own. Then I have to go to the menu [More]
I was partitioning my 160GB (I have about 70GB free space) with the Boot Camp assistant, when my computer kernel panicked. I had to restart, and when I logged in again I was missing about 10GB of space. I did an "Erase Free Space" w/ Disk Utilit [More]