nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

SQLException encounter while executing data trigger....

Ssafeeq

Summary

SQLException encounter while executing data trigger....

Content

Added a new parameter "P_ORDER_BY_TXT" to an existing concurrent program and it started failing with the aforementioned error message. I added the parameter to the XML file (.ie. Data defintion). and on the beforetrigger, it's calling a PLSQL package. The new parameter has nothing do with the PLSQL package but still added the parameter to the package and amended the beforetrigger as well.

<dataTemplate name=BPOTLRNRDRTCPROJ" description="PB OTL Rendered Timecard by Employee - Projects" defaultPackage="PBHXC_PROJTIMECARD_UTIL"><parameters><parameter name="p_org_id" dataType="number"/>        <parameter name="p_from_end_date" dataType="character"/><parameter name="p_to_end_date" dataType="character"/>        <parameter name="p_person_id" dataType="number"/>        <parameter name="p_project_id" dataType="number"/><parameter name="p_comments" dataType="character"/>        <parameter name="p_tc_details" dataType="character"/>        <parameter name="p_pb_hii" dataType="character"/>        <parameter name="P_TA" dataType="character"/>        <parameter name="P_CC" dataType="character"/>        <parameter name="p_order_by_txt" dataType="character"/></parameters>

BeforeReport Trigger

    <dataTrigger name="beforeReport" source="BPHXC_PROJTIMECARD_UTIL.Generate_otl_emp_rtc(:p_org_id                               ,:p_person_id                               ,:p_project_id                               ,:p_from_end_date                               ,:p_to_end_date                               ,:p_comments                               ,:p_tc_details                               ,:p_ta                               ,:p_cc                               ,:p_order_by_txt                               )"/><dataStructure>

+---------------------------------------------------------------------------+Parsons Time and Labor: Version : UNKNOWNCopyright (c) 1979, 1999, Oracle Corporation. All rights reserved.BPOTLRNRDRTCPROJ module: XXBP OTL Rendered Timecard by Employee - Projects+---------------------------------------------------------------------------+Current system time is 03-JUL-2017 14:16:44+---------------------------------------------------------------------------+XDO Data Engine Version No: 5.6.3Resp: 50034Org ID : 6436Request ID: 48910061All Parameters: p_org_id=6436:p_from_end_date=03-MAR-2017:p_to_end_date=31-MAR-2017:p_person_id=:p_project_id=4112836:p_comments=Y:p_tc_details=BOTH:p_pb_hii=PB:P_TA=:P_CC=:p_order_by_txt=TaskData Template Code: PBOTLRNRDRTCPROJData Template Application Short Name: PBHXCDebug Flag: N{p_comments=Y, P_CC=, p_person_id=, p_from_end_date=03-MAR-2017, p_tc_details=BOTH, p_to_end_date=31-MAR-2017, p_pb_hii=PB, p_project_id=4112836, P_TA=, p_org_id=6436, p_order_by_txt=Task}Calling XDO Data Engine...[070317_021651623][][EXCEPTION] SQLException encounter while executing data trigger....java.sql.SQLException: ORA-06550: line 12, column 25:PLS-00302: component 'P_ORDER_BY_TXT' must be declaredORA-06550: line 12, column 1:PL/SQL: Statement ignoredat oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)at oracle.apps.xdo.dataengine.XMLPGEN.executeTriggers(XMLPGEN.java:650)at oracle.apps.xdo.dataengine.XMLPGEN.processData(XMLPGEN.java:262)at oracle.apps.xdo.dataengine.XMLPGEN.processXML(XMLPGEN.java:215)at oracle.apps.xdo.dataengine.XMLPGEN.writeXML(XMLPGEN.java:254)at oracle.apps.xdo.dataengine.DataProcessor.processDataStructre(DataProcessor.java:390)at oracle.apps.xdo.dataengine.DataProcessor.processData(DataProcessor.java:355)at oracle.apps.xdo.oa.util.DataTemplate.processData(DataTemplate.java:334)at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:294)at oracle.apps.fnd.cp.request.Run.main(Run.java:157)+---------------------------------------------------------------------------+Start of log messages from FND_FILE+---------------------------------------------------------------------------++---------------------------------------------------------------------------+End of log messages from FND_FILE+---------------------------------------------------------------------------++---------------------------------------------------------------------------+Executing request completion options...Output file size: 0+------------- 1) PUBLISH -------------+Disabling requested Output Post Processing.  Nothing to process.  The output of the request is zero byte.+--------------------------------------+Finished executing request completion options.+---------------------------------------------------------------------------+Concurrent request completedCurrent system time is 03-JUL-2017 14:16:51+---------------------------------------------------------------------------+

Find more posts tagged with

Accepted answers

All comments

Brajesh Shukla-95078

Looks like there is problem in your package BPHXC_PROJTIMECARD_UTIL.Generate_otl_emp_rtc.

Could you please check parameter P_ORDER_BY_TXT is declared in your package

Ssafeeq

The package has the parameter with a name "p_order_by_txt" but still the concurrent program fails.

CREATE OR REPLACE PACKAGE APPS."XXBP_PROJTIMECARD_UTIL"AS   PROCEDURE StartReport (p_end_date DATE);   PROCEDURE PurgeTCDetails;   PROCEDURE GeneratePersonTC (p_person_id NUMBER, p_end_date DATE);   PROCEDURE GeneratePersonTCPA (p_person_id NUMBER, p_stop DATE); --from_date  date   FUNCTION GenerateTCHTML (p_person_id NUMBER, p_end_date DATE)      RETURN VARCHAR2;   PROCEDURE GenerateTCPADetails (p_person_id      NUMBER,                                  p_stop           DATE  --,p_from_date   date                                                       --,p_to_date   date                                  ,                                  p_start_time     DATE,                                  p_stop_time      DATE,                                  p_timecard_id    NUMBER);   PROCEDURE GeneratePersonTCMYLIST (p_person_id NUMBER, p_end_date DATE);   PROCEDURE Get_TA_info (p_user_id_in      IN     VARCHAR2,                          p_org_id_in       IN     NUMBER,                          p_ta_name1_out       OUT VARCHAR2,                          p_ta_email1_out      OUT VARCHAR2,                          p_ta_phone1_out      OUT VARCHAR2,                          p_ta_name2_out       OUT VARCHAR2,                          p_ta_email2_out      OUT VARCHAR2,                          p_ta_phone2_out      OUT VARCHAR2,                          p_ta_name3_out       OUT VARCHAR2,                          p_ta_email3_out      OUT VARCHAR2,                          p_ta_phone3_out      OUT VARCHAR2);   --1.4          4/27/2010     CastilloA        SCR #720   p_org_id          NUMBER;   p_person_id       NUMBER;   p_project_id      NUMBER;   p_from_end_date   VARCHAR2 (12);   p_to_end_date     VARCHAR2 (12);   p_comments        VARCHAR2 (1);   p_tc_details      VARCHAR2 (20);   --1.5          10/22/2010    CastilloA        SCR #000611   p_pb_hii          VARCHAR2 (10);   --1.5          10/22/2010    CastilloA        SCR #000611   --1.6          12/08/2010    BacayJ           SCR #00061 SCR - CR003870 PB OTL Rendered Timecard - ADD TA as a Selection AND Cost Center as a Selection   --Start   p_ta              VARCHAR2 (200);   p_cc              VARCHAR2 (200);   --1.6 End   FUNCTION Generate_otl_emp_rtc (p_org_id           NUMBER,                                  p_person_id        NUMBER,                                  p_project_id       NUMBER,                                  p_from_end_date    VARCHAR2,                                  p_to_end_date      VARCHAR2,                                  p_comments         VARCHAR2,                                  p_tc_details       VARCHAR2 ,                                  p_ta               VARCHAR2 DEFAULT NULL,                                  p_cc               VARCHAR2 DEFAULT NULL--                                  p_sort_by          VARCHAR2 DEFAULT NULL                                  ,p_order_by_txt VARCHAR2 DEFAULT NULL                                  )      RETURN BOOLEAN;--         1.7          03/08/2011    CastilloA        SCR #004946--1.4         4/27/2010     CastilloA        SCR #720END XXBP_PROJTIMECARD_UTIL;/

Brajesh Shukla-95078

Hi ,

You are calling BPHXC_PROJTIMECARD_UTIL.Generate_otl_emp_rtc from the Before report trigger but the code you have pasted is package XXBP_PROJTIMECARD_UTIL.

Could you have a look?

Ssafeeq

Sorry the messed up the package name - corrected package name on the xml but still getting the same error.

Brajesh Shukla-95078

Request you to upload correct package specification, body and XML data template...

Ssafeeq

Please see the XML, package Spec and body.

<dataTemplate name="BPOTLRNRDRTCPROJ" description="BP OTL Rendered Timecard by Employee - Projects" defaultPackage="BPHXC_PROJTIMECARD_UTIL"><parameters><parameter name="p_org_id" dataType="number"/><parameter name="p_from_end_date" dataType="character"/><parameter name="p_to_end_date" dataType="character"/><parameter name="p_person_id" dataType="number"/><parameter name="p_project_id" dataType="number"/><parameter name="p_comments" dataType="character"/><parameter name="p_tc_details" dataType="character"/><parameter name="p_BP_hii" dataType="character"/><parameter name="P_TA" dataType="character"/><parameter name="P_CC" dataType="character"/>        <parameter name="p_order_by_txt" dataType="character"/>        </parameters><dataQuery>      <sqlStatement name="Q_P">      <![CDATA[        SELECT 'Please provide Employee, Project, TA or Cost Center in the Parameter.' INC_PARAM        FROM DUAL        WHERE :p_person_id IS NULL          AND :p_project_id IS NULL          AND :p_ta IS NULL          AND :p_cc IS NULL      ]]>      </sqlStatement>      <sqlStatement name="Q_HDR">      <![CDATA[        SELECT           :p_comments CNT_DISP           ,:p_BP_hii p_BP_hii          ,ORG_ID          ,ORG_NAME          ,PERIOD_START_DATE           ,PERIOD_END_DATE          ,'Timecard Details For '||TO_CHAR(PERIOD_END_DATE,'DD-MON-RRRR') TCD_FOR          ,PERSON_ID           ,FULL_NAME||', '||EMPLOYEE_NUMBER||'  _______________________________' FULL_NAME          ,EMPLOYEE_NUMBER          ,ORGANIZATION          ,AWS          ,APPROVAL_STATUS || ', '||TO_CHAR(APPROVAL_DATE,'DD-MON-RRRR HH:MI AM') APPROVAL_STATUS          ,APPROVER_ID          ,APPROVER ||'  _________________________' APPROVER          ,COMMENTS          ,DAY1_AWS_DATE          ,DAY1_DATE          ,DAY2_DATE          ,DAY3_DATE          ,DAY4_DATE          ,DAY5_DATE          ,DAY6_DATE          ,DAY7_DATE          ,DAY1_AWS          ,DAY1          ,DAY2          ,DAY3          ,DAY4          ,DAY5          ,DAY6          ,DAY7          ,DAY1_AWS_DESC          ,DAY1_DESC          ,DAY2_DESC          ,DAY3_DESC          ,DAY4_DESC          ,DAY5_DESC          ,DAY6_DESC          ,DAY7_DESC          ,SUM(NVL(DAY1_AWS_REG_TOTAL,0)) DAY1_AWS_REG_TOTAL          ,SUM(NVL(DAY1_REG_TOTAL,0)) DAY1_REG_TOTAL          ,SUM(NVL(DAY2_REG_TOTAL,0)) DAY2_REG_TOTAL          ,SUM(NVL(DAY3_REG_TOTAL,0)) DAY3_REG_TOTAL          ,SUM(NVL(DAY4_REG_TOTAL,0)) DAY4_REG_TOTAL          ,SUM(NVL(DAY5_REG_TOTAL,0)) DAY5_REG_TOTAL          ,SUM(NVL(DAY6_REG_TOTAL,0)) DAY6_REG_TOTAL          ,SUM(NVL(DAY7_REG_TOTAL,0)) DAY7_REG_TOTAL          ,SUM(REG_TOTAL) REG_TOTAL          ,SUM(NVL(DAY1_AWS_OT_TOTAL,0)) DAY1_AWS_OT_TOTAL          ,SUM(NVL(DAY1_OT_TOTAL,0)) DAY1_OT_TOTAL          ,SUM(NVL(DAY2_OT_TOTAL,0)) DAY2_OT_TOTAL          ,SUM(NVL(DAY3_OT_TOTAL,0)) DAY3_OT_TOTAL          ,SUM(NVL(DAY4_OT_TOTAL,0)) DAY4_OT_TOTAL          ,SUM(NVL(DAY5_OT_TOTAL,0)) DAY5_OT_TOTAL          ,SUM(NVL(DAY6_OT_TOTAL,0)) DAY6_OT_TOTAL          ,SUM(NVL(DAY7_OT_TOTAL,0)) DAY7_OT_TOTAL          ,SUM(OT_TOTAL) OT_TOTAL          ,SUM(NVL(DAY1_AWS_TOTAL,0)) DAY1_AWS_TOTAL          ,SUM(NVL(DAY1_TOTAL,0)) DAY1_TOTAL          ,SUM(NVL(DAY2_TOTAL,0)) DAY2_TOTAL          ,SUM(NVL(DAY3_TOTAL,0)) DAY3_TOTAL          ,SUM(NVL(DAY4_TOTAL,0)) DAY4_TOTAL          ,SUM(NVL(DAY5_TOTAL,0)) DAY5_TOTAL          ,SUM(NVL(DAY6_TOTAL,0)) DAY6_TOTAL          ,SUM(NVL(DAY7_TOTAL,0)) DAY7_TOTAL          ,SUM(TOTAL) TOTAL        FROM HXC.BP_OTL_RTC_HEADER_TMP        GROUP BY          :p_comments          ,:p_BP_hii           ,ORG_ID          ,ORG_NAME          --,EXPENDITURE_ID          ,PERIOD_START_DATE           ,PERIOD_END_DATE          ,'Timecard Details For '||TO_CHAR(PERIOD_END_DATE,'DD-MON-RRRR')          ,PERSON_ID           ,FULL_NAME||', '||EMPLOYEE_NUMBER||'  _______________________________'          ,EMPLOYEE_NUMBER          ,ORGANIZATION          ,AWS          ,APPROVAL_STATUS || ', '||TO_CHAR(APPROVAL_DATE,'DD-MON-RRRR HH:MI AM')          ,APPROVER_ID          ,APPROVER ||'  _________________________'          ,COMMENTS          ,DAY1_AWS_DATE          ,DAY1_DATE          ,DAY2_DATE          ,DAY3_DATE          ,DAY4_DATE          ,DAY5_DATE          ,DAY6_DATE          ,DAY7_DATE          ,DAY1_AWS          ,DAY1          ,DAY2          ,DAY3          ,DAY4          ,DAY5          ,DAY6          ,DAY7          ,DAY1_AWS_DESC          ,DAY1_DESC          ,DAY2_DESC          ,DAY3_DESC          ,DAY4_DESC          ,DAY5_DESC          ,DAY6_DESC          ,DAY7_DESC                ORDER BY PERIOD_END_DATE DESC, FULL_NAME       ]]> </sqlStatement>      <sqlStatement name="Q_EXP">      <![CDATA[        SELECT          EXPENDITURE_ID        FROM HXC.BP_OTL_RTC_HEADER_TMP        WHERE ORG_ID = :ORG_ID          AND PERIOD_START_DATE = :PERIOD_START_DATE          AND PERIOD_END_DATE = :PERIOD_END_DATE          AND PERSON_ID = :PERSON_ID          AND AWS = :AWS        ORDER BY EXPENDITURE_ID        ]]>      </sqlStatement>      <sqlStatement name="Q_DTL">      <![CDATA[        SELECT           PROJECT_ID          ,PROJECT_NUMBER          ,PROJECT_NAME           ,TASK_ID          ,TASK_NUMBER          ,TASK_NAME          ,FLV.DESCRIPTION EXPENDITURE_TYPE          ,:AWS D_AWS          ,FRI_AWS DAY1_AWS          ,SAT DAY1          ,SUN DAY2          ,MON DAY3          ,TUE DAY4          ,WED DAY5          ,THU DAY6          ,FRI DAY7          ,TOTAL          ,TYPE_OF_ENTRY TYPEOFENTRY          ,CASE WHEN TYPE_OF_ENTRY = 'A' THEN             'T'           WHEN TYPE_OF_ENTRY = 'B' THEN             'R'           WHEN TYPE_OF_ENTRY = 'C' THEN             'A'           END TYPE_OF_ENTRY        FROM HXC.BP_OTL_RTC_FINAL_DTL_TMP          ,FND_LOOKUP_TYPES FL          ,FND_LOOKUP_VALUES FLV        WHERE ORG_ID = :ORG_ID          AND EXPENDITURE_ID = :EXPENDITURE_ID          AND FL.LOOKUP_TYPE ='BP_OTL_EXPENDITURE_TYPE'          AND FL.LOOKUP_TYPE = FLV.LOOKUP_TYPE          AND TRUNC(SYSDATE) BETWEEN TRUNC(FLV.START_DATE_ACTIVE) AND TRUNC(NVL(FLV.END_DATE_ACTIVE,SYSDATE))          AND FLV.LOOKUP_CODE = expenditure_type         ORDER BY EXPENDITURE_ID, sort_by,TYPEOFENTRY, project_number       ]]> </sqlStatement>      <sqlStatement name="Q_CMNTS">      <![CDATA[        SELECT           PROJECT_ID          ,PROJECT_NUMBER          ,PROJECT_NAME           ,TASK_ID          ,TASK_NUMBER          ,TASK_NAME          ,EXPENDITURE_ITEM_DATE          ,COMMENTS        FROM HXC.BP_OTL_RTC_COMMENTS_TMP        WHERE ORG_ID = :ORG_ID          AND EXPENDITURE_ID = :EXPENDITURE_ID          AND PERIOD_START_DATE = :PERIOD_START_DATE          AND PERIOD_END_DATE = :PERIOD_END_DATE          AND PERSON_ID = :PERSON_ID        ORDER BY EXPENDITURE_ID, PROJECT_NUMBER, TASK_NUMBER       ]]>      </sqlStatement></dataQuery>    <dataTrigger name="beforeReport" source="BPHXC_PROJTIMECARD_UTIL.Generate_otl_emp_rtc(:p_org_id                               ,:p_person_id                               ,:p_project_id                               ,:p_from_end_date                               ,:p_to_end_date                               ,:p_comments                               ,:p_tc_details                               ,:p_ta                               ,:p_cc                               )"/><dataStructure>        <group name="GP" source="Q_P">          <element name="GP_INC_PARAM" value="INC_PARAM"/>        </group><group name="GH" source="Q_HDR">          <element name="GH_BP_HII" value="p_BP_hii"/>          <element name="GH_CNT_DISP" value="CNT_DISP"/> <element name="GH_ORG_ID" value="ORG_ID"/>          <element name="GH_ORG_NAME" value="ORG_NAME"/>          <element name="GH_PERIOD_START_DATE" value="PERIOD_START_DATE"/>          <element name="GH_PERIOD_END_DATE" value="PERIOD_END_DATE"/>          <element name="GH_TCD_FOR" value="TCD_FOR"/>          <element name="GH_PERSON_ID" value="PERSON_ID"/>          <element name="GH_FULL_NAME" value="FULL_NAME"/>          <element name="GH_ORGANIZATION" value="ORGANIZATION"/>          <element name="GH_AWS" value="AWS"/>          <element name="GH_APPROVAL_STATUS" value="APPROVAL_STATUS"/>          <element name="GH_APPROVER" value="APPROVER"/>          <element name="GH_COMMENTS" value="COMMENTS"/>          <element name="GH_DAY1_AWS" value="DAY1_AWS"/>          <element name="GH_DAY1" value="DAY1"/>          <element name="GH_DAY2" value="DAY2"/>          <element name="GH_DAY3" value="DAY3"/>          <element name="GH_DAY4" value="DAY4"/>          <element name="GH_DAY5" value="DAY5"/>          <element name="GH_DAY6" value="DAY6"/>          <element name="GH_DAY7" value="DAY7"/>          <element name="GH_DAY1_AWS_DESC" value="DAY1_AWS_DESC"/>          <element name="GH_DAY1_DESC" value="DAY1_DESC"/>          <element name="GH_DAY2_DESC" value="DAY2_DESC"/>          <element name="GH_DAY3_DESC" value="DAY3_DESC"/>          <element name="GH_DAY4_DESC" value="DAY4_DESC"/>          <element name="GH_DAY5_DESC" value="DAY5_DESC"/>          <element name="GH_DAY6_DESC" value="DAY6_DESC"/>          <element name="GH_DAY7_DESC" value="DAY7_DESC"/>          <element name="GH_DAY1_AWS_REG_TOTAL" value="DAY1_AWS_REG_TOTAL"/>          <element name="GH_DAY1_REG_TOTAL" value="DAY1_REG_TOTAL"/>          <element name="GH_DAY2_REG_TOTAL" value="DAY2_REG_TOTAL"/>          <element name="GH_DAY3_REG_TOTAL" value="DAY3_REG_TOTAL"/>          <element name="GH_DAY4_REG_TOTAL" value="DAY4_REG_TOTAL"/>          <element name="GH_DAY5_REG_TOTAL" value="DAY5_REG_TOTAL"/>          <element name="GH_DAY6_REG_TOTAL" value="DAY6_REG_TOTAL"/>          <element name="GH_DAY7_REG_TOTAL" value="DAY7_REG_TOTAL"/>          <element name="GH_REG_TOTAL" value="REG_TOTAL"/>          <element name="GH_DAY1_AWS_OT_TOTAL" value="DAY1_AWS_OT_TOTAL"/>          <element name="GH_DAY1_OT_TOTAL" value="DAY1_OT_TOTAL"/>          <element name="GH_DAY2_OT_TOTAL" value="DAY2_OT_TOTAL"/>          <element name="GH_DAY3_OT_TOTAL" value="DAY3_OT_TOTAL"/>          <element name="GH_DAY4_OT_TOTAL" value="DAY4_OT_TOTAL"/>          <element name="GH_DAY5_OT_TOTAL" value="DAY5_OT_TOTAL"/>          <element name="GH_DAY6_OT_TOTAL" value="DAY6_OT_TOTAL"/>          <element name="GH_DAY7_OT_TOTAL" value="DAY7_OT_TOTAL"/>          <element name="GH_OT_TOTAL" value="OT_TOTAL"/>          <element name="GH_DAY1_AWS_TOTAL" value="DAY1_AWS_TOTAL"/>          <element name="GH_DAY1_TOTAL" value="DAY1_TOTAL"/>          <element name="GH_DAY2_TOTAL" value="DAY2_TOTAL"/>          <element name="GH_DAY3_TOTAL" value="DAY3_TOTAL"/>          <element name="GH_DAY4_TOTAL" value="DAY4_TOTAL"/>          <element name="GH_DAY5_TOTAL" value="DAY5_TOTAL"/>          <element name="GH_DAY6_TOTAL" value="DAY6_TOTAL"/>          <element name="GH_DAY7_TOTAL" value="DAY7_TOTAL"/>          <element name="GH_TOTAL" value="TOTAL"/>         <group name="GEXP" source="Q_EXP">                   <element name="GEXP_EXPENDITURE_ID" value="EXPENDITURE_ID"/>   <group name="GD" source="Q_DTL">  <element name="GD_PROJECT_NUMBER" value="PROJECT_NUMBER"/>            <element name="GD_PROJECT_NAME" value="PROJECT_NAME"/>            <element name="GD_TASK_NUMBER" value="TASK_NUMBER"/>            <element name="GD_TASK_NAME" value="TASK_NAME"/>            <element name="GD_EXPENDITURE_TYPE" value="EXPENDITURE_TYPE"/>            <element name="GD_AWS" value="D_AWS"/>            <element name="GD_DAY1_AWS" value="DAY1_AWS"/>            <element name="GD_DAY1" value="DAY1"/>            <element name="GD_DAY2" value="DAY2"/>            <element name="GD_DAY3" value="DAY3"/>            <element name="GD_DAY4" value="DAY4"/>            <element name="GD_DAY5" value="DAY5"/>            <element name="GD_DAY6" value="DAY6"/>            <element name="GD_DAY7" value="DAY7"/>            <element name="GD_TOTAL" value="TOTAL"/>            <element name="TYPE_OF_ENTRY" value="TYPE_OF_ENTRY"/> </group>          <group name="GC" source="Q_CMNTS">            <element name="GC_PROJECT_NUMBER" value="PROJECT_NUMBER"/>            <element name="GC_PROJECT_NAME" value="PROJECT_NAME"/>            <element name="GC_TASK_NUMBER" value="TASK_NUMBER"/>            <element name="GC_TASK_NAME" value="TASK_NAME"/>            <element name="GC_EXPENDITURE_ITEM_DATE" value="EXPENDITURE_ITEM_DATE"/>            <element name="GC_COMMENTS" value="COMMENTS"/>          </group>         </group></group></dataStructure></dataTemplate>

Package Spec:

CREATE OR REPLACE PACKAGE APPS."BPHXC_PROJTIMECARD_UTIL"AS      PROCEDURE StartReport (p_end_date DATE);   PROCEDURE PurgeTCDetails;   PROCEDURE GeneratePersonTC (p_person_id NUMBER, p_end_date DATE);   PROCEDURE GeneratePersonTCPA (p_person_id NUMBER, p_stop DATE); --from_date  date   FUNCTION GenerateTCHTML (p_person_id NUMBER, p_end_date DATE)      RETURN VARCHAR2;   PROCEDURE GenerateTCPADetails (p_person_id      NUMBER,                                  p_stop           DATE  --,p_from_date   date                                                       --,p_to_date   date                                  ,                                  p_start_time     DATE,                                  p_stop_time      DATE,                                  p_timecard_id    NUMBER);      PROCEDURE GeneratePersonTCMYLIST (p_person_id NUMBER, p_end_date DATE);   PROCEDURE Get_TA_info (p_user_id_in      IN     VARCHAR2,                          p_org_id_in       IN     NUMBER,                          p_ta_name1_out       OUT VARCHAR2,                          p_ta_email1_out      OUT VARCHAR2,                          p_ta_phone1_out      OUT VARCHAR2,                          p_ta_name2_out       OUT VARCHAR2,                          p_ta_email2_out      OUT VARCHAR2,                          p_ta_phone2_out      OUT VARCHAR2,                          p_ta_name3_out       OUT VARCHAR2,                          p_ta_email3_out      OUT VARCHAR2,                          p_ta_phone3_out      OUT VARCHAR2);   p_org_id          NUMBER;   p_person_id       NUMBER;   p_project_id      NUMBER;   p_from_end_date   VARCHAR2 (12);   p_to_end_date     VARCHAR2 (12);   p_comments        VARCHAR2 (1);   p_tc_details      VARCHAR2 (20);   p_pb_hii          VARCHAR2 (10);   p_ta              VARCHAR2 (200);   p_cc              VARCHAR2 (200);   FUNCTION Generate_otl_emp_rtc (p_org_id           NUMBER,                                  p_person_id        NUMBER,                                  p_project_id       NUMBER,                                  p_from_end_date    VARCHAR2,                                  p_to_end_date      VARCHAR2,                                  p_comments         VARCHAR2,                                  p_tc_details       VARCHAR2,                                  p_ta               VARCHAR2 DEFAULT NULL,                                  p_cc               VARCHAR2 DEFAULT NULL                                  ,p_order_by_txt VARCHAR2 DEFAULT NULL                                  )      RETURN BOOLEAN;END BPHXC_PROJTIMECARD_UTIL;/

Brajesh Shukla-95078

....Please follow the below steps

1. Do not declare your trigger with IN Parameter.

Signature of before report should be

FUNCTION Generate_otl_emp_rtc RETURN BOOLEAN;

2. You can directly refer the XML Data template parameters to trigger body. So no need to call the beforereport trigger by passing parameters.

Call like

3. In your databse package function you can directly refer data template parameter as below

FUNCTION Generate_otl_emp_rtc

RETURN BOOLEAN;

dbms_output.put_line('Org Id is : '||p_org_id); --this is example to use p_org_id from data template directly to trigger

END IF;

Ssafeeq

I did tried the changes suggested by you but unfortunately hitting the same error again.

Current system time is 12-JUL-2017 09:36:54+---------------------------------------------------------------------------+XDO Data Engine Version No: 5.6.3Resp: 50036Org ID : 6436Request ID: 49322037All Parameters: p_org_id=6436:p_from_end_date=06-JAN-2017:p_to_end_date=13-JAN-2017:p_person_id=:p_project_id=4112836:p_comments=Y:p_tc_details=BOTH:p_BP_hii=BP:P_TA=:P_CC=:p_order_by_txt=TaskData Template Code: BPOTLRNRDRTCPROJData Template Application Short Name: BPHXCDebug Flag: N{p_comments=Y, P_CC=, p_person_id=, p_from_end_date=06-JAN-2017, p_tc_details=BOTH, p_to_end_date=13-JAN-2017, p_BP_hii=BP, p_project_id=4112836, P_TA=, p_org_id=6436, p_order_by_txt=Task}Calling XDO Data Engine...[071217_093700082][][EXCEPTION] SQLException encounter while executing data trigger....java.sql.SQLException: ORA-06550: line 12, column 25:PLS-00302: component 'P_ORDER_BY_TXT' must be declaredORA-06550: line 12, column 1:PL/SQL: Statement ignoredat oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)at oracle.apps.xdo.dataengine.XMLPGEN.executeTriggers(XMLPGEN.java:650)at oracle.apps.xdo.dataengine.XMLPGEN.processData(XMLPGEN.java:262)at oracle.apps.xdo.dataengine.XMLPGEN.processXML(XMLPGEN.java:215)at oracle.apps.xdo.dataengine.XMLPGEN.writeXML(XMLPGEN.java:254)at oracle.apps.xdo.dataengine.DataProcessor.processDataStructre(DataProcessor.java:390)at oracle.apps.xdo.dataengine.DataProcessor.processData(DataProcessor.java:355)at oracle.apps.xdo.oa.util.DataTemplate.processData(DataTemplate.java:334)at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:294)at oracle.apps.fnd.cp.request.Run.main(Run.java:157)+---------------------------------------------------------------------------+Start of log messages from FND_FILE

<dataTrigger name="beforeReport" source="PBHXC_PROJTIMECARD_UTIL.Generate_otl_emp_rtc"/>

FUNCTION Generate_otl_emp_rtcRETURN BOOLEAN ISBEGIN    insert into xxdebug values(1,'Org ID:'|| p_org_id, sysdate);    RETURN TRUE;EXCEPTIONWHEN OTHERS THEN    RETURN false;END    Generate_otl_emp_rtc;

Brajesh Shukla-95078

After all change still we are getting same error and also before report trigger package(BPHXC_PROJTIMECARD_UTIL.Generate_otl_emp_rtc) what I got from you is different than what message is appearing in latest message(PBHXC_PROJTIMECARD_UTIL.Generate_otl_emp_rtc) ...this may indicate that, are we using same code what we are changing? could you please put some messages on the code Generate_otl_emp_rtc...and see in the log is it appears?

Carlos Carvalho

Hello,

The error you´re getting it´s because as you said:

Added a new parameter "P_ORDER_BY_TXT" to an existing concurrent program and it started failing with the aforementioned error message. I added the parameter to the XML file (.ie. Data defintion). and on the beforetrigger, it's calling a PLSQL package.

therefore you need to declare it in the package spec as global variable with exact same name, which you did not do as seeing in the package code you paste, there is only:

 --1.4          4/27/2010     CastilloA        SCR #720     p_org_id          NUMBER;     p_person_id       NUMBER;     p_project_id      NUMBER;     p_from_end_date   VARCHAR2 (12);     p_to_end_date     VARCHAR2 (12);     p_comments        VARCHAR2 (1);     p_tc_details      VARCHAR2 (20);     --1.5          10/22/2010    CastilloA        SCR #000611     p_pb_hii          VARCHAR2 (10);     --1.5          10/22/2010    CastilloA        SCR #000611     --1.6          12/08/2010    BacayJ           SCR #00061 SCR - CR003870 PB OTL Rendered Timecard - ADD TA as a Selection AND Cost Center as a Selection     --Start     p_ta              VARCHAR2 (200);     p_cc              VARCHAR2 (200);         --1.6 End

Add P_ORDER_BY_TXT varchar2(200);

Btw you´re 'p_pb_hii' is <parameter name="p_BP_hii in other order - check that too.

Regards,

Carlos