Categories
- All Categories
- 5 Oracle Analytics Sharing Center
- 11 Oracle Analytics Lounge
- 196 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.5K Oracle Analytics Forums
- 6K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 68 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture
SQLException encounter while executing data trigger....
Ssafeeq
Rank 3 - Community Apprentice
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+---------------------------------------------------------------------------+
0
Answers
-
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
0 -
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;/
0 -
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?
0 -
Sorry the messed up the package name - corrected package name on the xml but still getting the same error.
0 -
Request you to upload correct package specification, body and XML data template...
0 -
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;/
0 -
....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
<dataTrigger name="beforeReport" source="BPHXC_PROJTIMECARD_UTIL.Generate_otl_emp_rtc"/>
3. In your databse package function you can directly refer data template parameter as below
FUNCTION Generate_otl_emp_rtc
RETURN BOOLEAN;
IS
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;
0 -
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;
0 -
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?
0 -
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
0