http://www.oraerp.com/ Wed, 19 Jun 2013 10:53:05 +0000 MyBB http://www.oraerp.com/Thread-XML-Publisher-in-R11-5-10-for-Bank-Check-Printing Mon, 01 Oct 2012 13:18:25 -0500 http://www.oraerp.com/Thread-XML-Publisher-in-R11-5-10-for-Bank-Check-Printing
If there an XML Publisher check printing program/template for Oracle R11.5.10 available for a bank of america check print?

Thanks in advance.

Prakash]]>
If there an XML Publisher check printing program/template for Oracle R11.5.10 available for a bank of america check print?

Thanks in advance.

Prakash]]> http://www.oraerp.com/Thread-How-to-customize-dunning-letters-in-R12 Fri, 12 Aug 2011 09:15:58 -0500 http://www.oraerp.com/Thread-How-to-customize-dunning-letters-in-R12 1) create xml publisher data definition under the application "Collections" ( no need for an xml that contains any SQL )

2) create xml publisher template under the application "Collectïons" and connect the data definition
3) (step 3 is only if you use Collections Dunning and not strategies) navigate to resp "Receivables Manager" path "/Setup/Collections/Setup Checklist" and click on "view existing" for create dunning plan. Then find an existing one or create a new one. In step 3 of the dunning plan connect your custom template to the dunning level that you want your custom template to be enabled for.
4) navigate to resp "Collections Administrator" path "/Administration/Manage Queries" and create a new query there that holds your custom SQL. I recommend to look at the seeded queries to see which bind variables are used for the query. They differ per level of query ( customer/account/etc ) and you will need to get them right or you end up with the wrong data or an error in the dunning program.
5) navigate to resp "Collections Administrator" path "/Administration/Manage Template Query" and connect your query to your template.

-- now you are ready to run : we are just using Advanced Collections Dunning (without strategies) so for me it is:
6) make an invoice on a cust that is past the due date
7) run IEX: Scoring Engine Harnass
8) run IEX: Send dunnings for Delinquent Customers
9) this starts : Oracle Collections Delivery XML Process (IEX: Bulk XML Delivery Manager) which will run your template, fetch the data and create the output.]]> 1) create xml publisher data definition under the application "Collections" ( no need for an xml that contains any SQL )

2) create xml publisher template under the application "Collectïons" and connect the data definition
3) (step 3 is only if you use Collections Dunning and not strategies) navigate to resp "Receivables Manager" path "/Setup/Collections/Setup Checklist" and click on "view existing" for create dunning plan. Then find an existing one or create a new one. In step 3 of the dunning plan connect your custom template to the dunning level that you want your custom template to be enabled for.
4) navigate to resp "Collections Administrator" path "/Administration/Manage Queries" and create a new query there that holds your custom SQL. I recommend to look at the seeded queries to see which bind variables are used for the query. They differ per level of query ( customer/account/etc ) and you will need to get them right or you end up with the wrong data or an error in the dunning program.
5) navigate to resp "Collections Administrator" path "/Administration/Manage Template Query" and connect your query to your template.

-- now you are ready to run : we are just using Advanced Collections Dunning (without strategies) so for me it is:
6) make an invoice on a cust that is past the due date
7) run IEX: Scoring Engine Harnass
8) run IEX: Send dunnings for Delinquent Customers
9) this starts : Oracle Collections Delivery XML Process (IEX: Bulk XML Delivery Manager) which will run your template, fetch the data and create the output.]]> http://www.oraerp.com/Thread-Workflow-Tables-and-Queries Sun, 02 Jan 2011 02:56:05 -0600 http://www.oraerp.com/Thread-Workflow-Tables-and-Queries
Query1: Accepts Workflow itemtype / shortname as input parameter and will all the activities involved along with the status and user name to whom the current activity is assigned.

Query2: Accepts workflow itemtype and activity as input variables and the results will provide the time frame explaining from how long the activity is pending along with the username whose action is req

WORKFLOW TABLES
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS



SELECT * FROM WF_USER_ROLES



SELECT * FROM WF_ROLES



SELECT * FROM WF_ITEMS



SELECT * FROM WF_ITEM_ATTRIBUTES



SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES



SELECT * FROM WF_ITEM_ATTRIBUTES_TL



SELECT * FROM WF_ACTIVITIES



SELECT * FROM WF_ACTIVITIES_TL



SELECT * FROM WF_ACTIVITY_ATTRIBUTES



SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL



SELECT * FROM WF_ACTIVITY_TRANSITIONS



SELECT * FROM WF_DEFERRED--WF_CONTROL



SELECT * FROM WF_ACTIVITY_ATTR_VALUES

WHERE NAME LIKE '%MASTER%'

AND PROCESS_ACTIVITY_ID

IN(

SELECT *-- PROCESS_ACTIVITY

FROM WF_ITEM_ACTIVITY_STATUSES

WHERE ITEM_TYPE = 'ERP'

AND ITEM_KEY ='63865'

)



SELECT * FROM WF_ITEM_TYPES



SELECT * FROM WF_LOOKUPS_TL



SELECT * FROM WF_NOTIFICATIONS

WHERE MESSAGE_TYPE ='ERP'

ORDER BY BEGIN_DATE DESC



SELECT * FROM WF_NOTIFICATION_ATTRIBUTES



SELECT * FROM WF_MESSAGES



SELECT * FROM WF_MESSAGES_TL



SELECT * FROM WF_MESSAGE_ATTRIBUTES



SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL



SELECT * FROM WF_ETS



SELECT * FROM WF_PROCESS_ACTIVITIES

LIST OF ACTIVITIES FOR AN ITEMTYPE

SELECT A.ITEM_KEY,

B.ACTIVITY_NAME,

A.ACTIVITY_STATUS,

A.ACTIVITY_RESULT_CODE,

A.ASSIGNED_USER,

A.BEGIN_DATE,

A.END_DATE

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

AND A.ITEM_KEY = 64077

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')


TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING
SELECT B.ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,

COUNT(B.ACTIVITY_NAME) TOTAL_PENDING

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

--AND A.ITEM_KEY = 1131

AND END_DATE IS NULL

AND ACTIVITY_STATUS != 'ERROR'

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

GROUP BY ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)

ORDER BY ACTIVITY_NAME,

PENDING_FROM_NO_OF_DAYS

LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS


SELECT SUM(TOTAL_PENDING) PENDING_LESS_THAN_5DAYS

FROM

(SELECT B.ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,

COUNT(B.ACTIVITY_NAME) TOTAL_PENDING

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

--AND A.ITEM_KEY = 1131

AND END_DATE IS NULL

AND ACTIVITY_STATUS != 'ERROR'

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

GROUP BY ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)

ORDER BY ACTIVITY_NAME,

PENDING_FROM_NO_OF_DAYS ) FIVE_DAYS

WHERE FIVE_DAYS.PENDING_FROM_NO_OF_DAYS < 5

Your suggestions are welcome to improve ........ more you share, more you learn]]>
Query1: Accepts Workflow itemtype / shortname as input parameter and will all the activities involved along with the status and user name to whom the current activity is assigned.

Query2: Accepts workflow itemtype and activity as input variables and the results will provide the time frame explaining from how long the activity is pending along with the username whose action is req

WORKFLOW TABLES
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS



SELECT * FROM WF_USER_ROLES



SELECT * FROM WF_ROLES



SELECT * FROM WF_ITEMS



SELECT * FROM WF_ITEM_ATTRIBUTES



SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES



SELECT * FROM WF_ITEM_ATTRIBUTES_TL



SELECT * FROM WF_ACTIVITIES



SELECT * FROM WF_ACTIVITIES_TL



SELECT * FROM WF_ACTIVITY_ATTRIBUTES



SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL



SELECT * FROM WF_ACTIVITY_TRANSITIONS



SELECT * FROM WF_DEFERRED--WF_CONTROL



SELECT * FROM WF_ACTIVITY_ATTR_VALUES

WHERE NAME LIKE '%MASTER%'

AND PROCESS_ACTIVITY_ID

IN(

SELECT *-- PROCESS_ACTIVITY

FROM WF_ITEM_ACTIVITY_STATUSES

WHERE ITEM_TYPE = 'ERP'

AND ITEM_KEY ='63865'

)



SELECT * FROM WF_ITEM_TYPES



SELECT * FROM WF_LOOKUPS_TL



SELECT * FROM WF_NOTIFICATIONS

WHERE MESSAGE_TYPE ='ERP'

ORDER BY BEGIN_DATE DESC



SELECT * FROM WF_NOTIFICATION_ATTRIBUTES



SELECT * FROM WF_MESSAGES



SELECT * FROM WF_MESSAGES_TL



SELECT * FROM WF_MESSAGE_ATTRIBUTES



SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL



SELECT * FROM WF_ETS



SELECT * FROM WF_PROCESS_ACTIVITIES

LIST OF ACTIVITIES FOR AN ITEMTYPE

SELECT A.ITEM_KEY,

B.ACTIVITY_NAME,

A.ACTIVITY_STATUS,

A.ACTIVITY_RESULT_CODE,

A.ASSIGNED_USER,

A.BEGIN_DATE,

A.END_DATE

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

AND A.ITEM_KEY = 64077

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')


TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING
SELECT B.ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,

COUNT(B.ACTIVITY_NAME) TOTAL_PENDING

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

--AND A.ITEM_KEY = 1131

AND END_DATE IS NULL

AND ACTIVITY_STATUS != 'ERROR'

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

GROUP BY ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)

ORDER BY ACTIVITY_NAME,

PENDING_FROM_NO_OF_DAYS

LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS


SELECT SUM(TOTAL_PENDING) PENDING_LESS_THAN_5DAYS

FROM

(SELECT B.ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,

COUNT(B.ACTIVITY_NAME) TOTAL_PENDING

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

--AND A.ITEM_KEY = 1131

AND END_DATE IS NULL

AND ACTIVITY_STATUS != 'ERROR'

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

GROUP BY ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)

ORDER BY ACTIVITY_NAME,

PENDING_FROM_NO_OF_DAYS ) FIVE_DAYS

WHERE FIVE_DAYS.PENDING_FROM_NO_OF_DAYS < 5

Your suggestions are welcome to improve ........ more you share, more you learn]]> http://www.oraerp.com/Thread-Data-to-be-displayed-from-top-of-the-page Thu, 16 Dec 2010 04:27:27 -0600 http://www.oraerp.com/Thread-Data-to-be-displayed-from-top-of-the-page
I have a report which has 3 frames.

1st frames vertical elasticity is variable and other 2 frames vertical elasticity is fixed.

So when my 1st frame exceeds more than 1 page,the data of remaining frames doesn't continue along with the 1st frame,it displays the data where the frame has been placed in layout i.e 2nd & 3rd frames displays the data where the frames are placed in the layout leaving blank space on the top

So i want to display the data continuously after the 1st frame without any blank space and without any page breaks.

Thanks in advance,]]>
I have a report which has 3 frames.

1st frames vertical elasticity is variable and other 2 frames vertical elasticity is fixed.

So when my 1st frame exceeds more than 1 page,the data of remaining frames doesn't continue along with the 1st frame,it displays the data where the frame has been placed in layout i.e 2nd & 3rd frames displays the data where the frames are placed in the layout leaving blank space on the top

So i want to display the data continuously after the 1st frame without any blank space and without any page breaks.

Thanks in advance,]]> http://www.oraerp.com/Thread-Parameter-form-with-static-list Thu, 07 Oct 2010 03:51:11 -0500 http://www.oraerp.com/Thread-Parameter-form-with-static-list One parameter, Status, has a predifined list of values like 'New','WIP','Completed'.
So i've added these to the List of values for that parameter.
Now i also want to add null in this list, because user may or maynot give value for this parameter, while running the report.
If they want to see records with all these status, then they will not select any of the above mentioned 3 status(ie by default list item will have a null value)

Is there a way to do that?]]> One parameter, Status, has a predifined list of values like 'New','WIP','Completed'.
So i've added these to the List of values for that parameter.
Now i also want to add null in this list, because user may or maynot give value for this parameter, while running the report.
If they want to see records with all these status, then they will not select any of the above mentioned 3 status(ie by default list item will have a null value)

Is there a way to do that?]]>