Collection of Workflow Administration Scripts and/or Concurrent Program

Workflow Scripts: Source Oracle Documentations

The below are the collection of Workflow Administration Scripts and/or Concurrent Program.

1. FNDWFPR

For Oracle Workflow embedded in Oracle Applications, use the standard concurrent program FNDWFPR ”Purge Obsolete Workflow Runtime Data” to purge old data from the Oracle Workflow runtime tables regularly. Navigate to the Submit Requests form in Oracle Applications to submit the Purge Obsolete Workflow Runtime Data concurrent program. When you install and set up Oracle Applications and Oracle Workflow, your system administrator needs to add this concurrent program to a request security group for the responsibility that you want to run this program from.

You can supply the following parameters for the ”Purge Obsolete Workflow Runtime Data” concurrent program:

Item Type — The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.

Item Key — The item key to purge. Leaving this field blank defaults to purging the runtime data for all item keys.

Age — Minimum age of data to purge, in days.’

2. wfbkgchk.sql

Use wfbkgchk.sql to get a list of all activities waiting to be processed by the background engine the next time it runs.

Usage : sqlplus <user/pwd> @wfbkgchk

3. wfdirchk.sql

Use wfdirchk.sql to check for the following conditions in your directory service data model:

a. Invalid internal names that contain the characters ’#’, ’:’,
or ’/’ in WF_USERS.

b. Invalid compound names in WF_USERS or WF_ROLES.

c. Duplicate names in WF_USERS or WF_ROLES.

d. Multiple names in WF_USERS or WF_ROLES linked to the same row in the original repository.

e. Missing display names in WF_USERS or WF_ROLES.

f. Invalid Notification Preference or null email address if the Notification Preference is MAILTEXT, MAILHTML, or SUMMARY in
WF_USERS or WF_ROLES.

g. Invalid Status in WF_USERS.

h. Rows in WF_USERS that do not have a corresponding row in WF_ROLES.

i. Invalid internal names in WF_ROLES that contain the characters ’#’ or ’/’ or have a length greater than 30 characters.

j. Invalid user/role foreign key in WF_USER_ROLES.

k. Missing user/role in WF_USER_ROLES (every user must participate in its own role).

l. Duplicate rows in WF_USER_ROLES.

wfdirchk.sql should return no rows to ensure that your directory service data model is correct.

Usage:sqlplus <user/pwd> @wfdirchk

4. wfntfsh.sql

Use wfntfsh.sql to display status information about a particular notification, given its notification ID.

Usage:sqlplus <user/pwd> @wfntfsh <notification_id>

5. wfrefchk.sql

Use wfrefchk.sql to check for invalid workflow data that is missing primary key data for a foreign key.

Usage:sqlplus <user/pwd> @wferfchk

6. wfretry.sql

Use wfretry.sql to display a list of activities that have encountered an error for a given process instance and then specify whether to skip,
retry, or reset any one of those errored activities.

Usage:sqlplus <user/pwd> @wfretry <item_type> <item_key>

Provide an item type and item key to uniquely identify an item or process instance. The script first returns the list of errored
activities by label name. The script then prompts you for the label name of an activity that you wish to skip, retry, or reset. If you
choose skip, then you must also specify the result that you want the skipped activity to have.

7. wfstat.sql

Use wfstat.sql to display a developer status report for an indicated item. The output is 132 characters per line.

Usage:sqlplus <user/pwd> @wfstat <item_type> <item_key>

8. wfstatus.sql

Use wfstatus.sql to display an end user status report for an indicated item. The output is 132 characters per line.

Usage:sqlplus <user/pwd> @wfstatus <item_type> <item_key>

9. wfstdchk.sql

Use wfstdchk.sql to check and report any problems found in the Oracle Workflow data model. For example, this script will report any function
activities that reference invalid functions and scan the tables of each workflow process definition object to verify that each row has a valid
internal name and display name.

Usage:sqlplus <user/pwd> @wfstdchk

10. wfver.sql

Use wfver.sql to display the version of the Oracle Workflow server, the status and version of the Oracle Workflow PL/SQL packages, and the
version of the Oracle Workflow views installed.

Usage:sqlplus <user/pwd> @wfver

11. wfverchk.sql

Use wfverchk.sql if you suspect that problems arising in your workflow process are due to multiple versions of an activity being active
simultaneously. This script identifies errors in versions of activities that cause multiple versions to appear to be active at once.

Usage:sqlplus <user/pwd> @wfverchk

12. wfverupd.sql

Use wfverupd.sql to correct problems arising in your workflow process that are due to multiple versions of an activity being active simultaneously. This script identifies and corrects errors in versions of activities that cause multiple versions to appear to be active at once.

Usage:sqlplus <user/pwd> @wfverupd

About the Author AppsDBA

Leave a Comment:

8 comments
David Haimes says January 20, 2008

Nice reference. If you don’t run the Purge program in 1. above you get all kinds of tablespace errors and unhappy users, as I found out to my cost!

Reply
virendra says January 25, 2008

how to tell an archived log has been applied in the standby, because we can’t purge an achived log unless it is transfered to the standby DB and applied.

and please refer me some document address for oracle DATA GUARD

Reply
Atul says January 25, 2008

Check V$LOG_HISTORY view on standby site

for more info check

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm#i1035175

Reply
Mike says December 30, 2008

That was great Ramnik keep up the good work

Reply
khaja says January 25, 2011

hi atul
why we are using workflow what happen if we don’t configure workflow?
please tell me
thank you,

Reply
uppi says January 12, 2015

Sundaram Finance
iExpense Training Manuals

Approval Process

KASPL
April 2014

This report contains 15 pages

iExpense Training Manuals.docx

Document review and approval
Revision history
Version Author Date Revision
1 Tushar James 30-Apr-2014

This document has been reviewed by
Reviewer Date reviewed
1 Sameer Wadhawan 27-Jun-14
2
3
4
5
This document has been approved by
Subject matter experts
Name Signature Date reviewed
1
2
3
4
5

Contents
1 Expense Report Approval by Supervisor 4
1.1 Navigation 4
1.2 Objective 4
1.3 Approval Notification 4
1.4 Approve Expense Report 6
1.5 Reject Expense Report 6
1.6 Request More Information 7
1.7 Re-Assign Notification to another approver 9
2 Setting Vacation Rules 12
2.1 Navigation 12
2.2 Objective 12
2.3 Setting Vacation Rules 12
2.4 Ending Vacation Rules 15

1 Expense Report Approval by Supervisor

1.1 Navigation
Log on to your Oracle Home page. You can navigate to the iExpense responsibility for notifications specific to iExpense.

1.2 Objective
To raise official expense claims and approve expense claims in the Oracle system.
1.3 Approval Notification
Once an expense report is submitted for approval by the employee, the same shows in the notification list of the approver. The approver needs to open the notification and take an action on the expense report.

Click on the notification to view the details of the notification and take any action.

The notification contains various sections.
– General Information about the expense report.
– Instructions for the Approver while taking an action.
– Details of the expenses that have been entered in the expense report
– Previously submitted expenses (Shows up to previously submitted 10 expense lines)
– Action History of the Expense Report (Shows details of approval actions taken previously on the expense report, if any)
– Link to open the expense report (To view additional details, if required)
– Response Note, which can be filled by the approver at the time of taking action. This note can be viewed by the employee as well as the finance team member, who will verify the bills.

1.4 Approve Expense Report

If all details of the expense report are found satisfactory, the approver can enter a response note (optional) and click on the approve button.
This will close the notification in the approver’s list of open notifications; send a notification to the preparer intimating about the approval and update the status of the expense report, with status as ‘Pending Payables Approval’.

1.5 Reject Expense Report
If the approver disapproves of the expense report, he/she can enter a response note and click on the reject button.
This will close the notification in the approver’s list of open notifications; send a notification to the preparer intimating about the rejection and update the status of the expense report as ‘Rejected’. Based on the approver’s comments, the preparer can modify the same expense report and re-submit for approval.
This will generate a fresh notification in the approver’s list of notifications, for his/her action.

1.6 Request More Information

An approver can request for more information from the preparer or any other employee in the organization, before approving the expense report.

When an approver clicks on the ‘Request Information’ button, the following screen opens.
By default, preparer’s name is populated.

If the approver wants to request information from some employee in the organization, select ‘Any User’ and search for the employee’s name.

Enter the ‘Information Requested’ field with the question and click on ‘Submit’.
This will send a notification to the employee/other user for the information. Once they enter their response and submit the same, a new notification comes to the approver, and the Action History section of the notification gets updated with the details entered by the employee.

1.7 Re-Assign Notification to another approver

An approver can delegate or transfer the approval action to another approver in the organization. This can be done by clicking on the ‘Reassign’ button in the notification.

Once you click on the re assign button the following screen would appear.

Enter the new approver’s name in the Assignee field.
If ‘Delegate your response’ is chosen, then the system records the action as action taken by the original approver.
If ‘Transfer notification ownership’ is chosen at the time of reassigning the notification, then the action is recorded as the new approver’s action. A notification is sent to the employee informing about the transfer of expense report for approval. The new approver should have the authorized approval authority for the given operating unit and department of the expense report.
Comments can be entered by the original approver, which will be visible to the new approver in the action history section of the notification.

Click ‘Submit’.
Once the notification is transferred to another employee for approval, a notification is sent to the employee informing the same.

2 Setting Vacation Rules

2.1 Navigation
iExpense responsibility -> Expense Home -> Vacation rule
2.2 Objective
To assign another person to take action on any expense claim on your behalf
2.3 Setting Vacation Rules
Log in to the iExpense responsibility. Below the Expenses Home page there is a link “Vacation Rules”. Click on the link.

Click on Create Rule.

Select the item types as expense and click on Next.

Select all and click on Next.

Select the start date and end date of your vacation. Your Notifications would be assigned to the person specified during this period.
Set and optional message if required. Select the employee whom you want to assign your notifications. Then click on Apply

You can click on update if you want to edit or send this rule.

2.4 Ending Vacation Rules

Click on vacation rules link on your expenses home page.

Click on update against the rule you want to edit.

Either clear the data on all fields or click on ‘Deliver Notifications to me regardless of any rules.’ To deactivate the rule.

————————End of Document ————————

Reply
uppi says January 12, 2015

Sundaram Finance
Internet Expenses User Manual

KASPL
April 2014
This report contains 22 pages

UserManual_iExpenses_SUNDARAM FINANCE

Document review and approval
Revision history
Version Author Date Revision
1 Asit Agarwal 24-Apr-2014

This document has been reviewed by
Reviewer Date reviewed
1 Sameer Wadhawan 27-Jun-14
2
3
4
5
This document has been approved by
Subject matter experts
Name Signature Date reviewed
1
2
3
4
5

Contents
1 Expense Report Creation 3
1.1 Navigation 3
1.2 Objective 3
1.3 Create Expense Report 3
1.4 Enter Basic Expense Claim Information 4
1.4.1 Cost Centre and Approver Details 4
1.5 Enter Expense Details 5
1.5.1 Enter ‘Receipt Based Expenses’ Details 5
1.5.2 Entering Location Information for the 3 Travel Templates only 7
1.5.3 Location Field- Hotel Stay Base Rent and Travel Ticket Expense Types 8
1.5.4 Attaching receipts to the expense report. 10
1.6 Reviewing you expense report 11
1.6.1 Submitting the expense report 12
2 Online Tracking of Submitted Expense Report 13
2.1 Navigation 13
2.2 Objective 13
2.3 Tracking of Submitted Expense Reports 13
2.4 ‘Update Expense Reports’ Section 14
2.5 Receipt Status 14
3 Notifications 16
3.1 Navigation 16
3.2 Objective 16
3.3 Notifications 16
4 Withdraw a Submitted Expense Report 17
4.1 Navigation 17
4.2 Objective 17
4.3 Withdraw Submitted Claims 17
5 Duplicating an Expense Report 19
5.1 Navigation 19
5.2 Objective 19
5.3 Duplicate Submitted Claims 19
6 Assigning someone to file expense claim on your behalf 20
7 Creating expense report via spread sheet. 22
8 Appendix 24

1 Expense Report Creation

1.1 Navigation
iExpense user responsibility -> Create Expense Report

1.2 Objective
To raise official expense claims in the Oracle system. In Sundaram Finance, the approval hierarchy is as follows. The expense report after submission goes to the immediate manager for approval. Post his approval the expense claim would then go to the Finance auditor for his approval. In case the immediate supervisor does not approve within 3 days , it will automatically go the next supervisor for approval.

1.3 Create Expense Report

Log on to Oracle using your User ID and Password provided by the ERP team. Select the iExpense responsibility. The responsibility would be provided according to your business group. For e.g. for SF employees, the responsibility name would be, SFL iExpense User.

Click on “Create Expense Report” button to start entering information of a new expense claim.

1.4 Enter Basic Expense Claim Information

Select the template and enter the purpose for the expense claim. Then click on next. Please note that the department of the user and the first Approver could also be the immediate manager, are editable, where as Re-Imbursement Currency and Name of the employee are not. The department and reimbursement currency are defaulted directly from the HRMS records of the employee and ledger currency, respectively. The approver would have to be selected by the user.

1.4.1 Cost Centre and Approver Details
Note: Enter the following details in this screen.
S No. Field Type Description
1 Name Mandatory Employee Name defaults. This would be picked up from the login details.
2 Function/ Department Mandatory/ Editable This value defaults the employees department
3 Reimbursement Currency Mandatory/ Non-Editable This value defaults from the ledger currency
4 Expense Template Mandatory Employee should choose template from the LOV attached with this field.
5 Purpose Not Mandatory Employee can enter the purpose of the expenses.
6 Approver Mandatory/ Editable The default supervisor would appear automatically , although the user can still select and change the approver
Travel Templates only
7 Travel From Date Mandatory/ Editable From travel date would be entered
8 Travel To Date Mandatory/ Editable To travel date would be entered
9 Travel From City Mandatory/ Editable City source would be selected from drop down menu
10 Travel To City Mandatory/ Editable Destination city would have to be entered
11 Purpose Category Mandatory/ Editable Purpose category would be selected from drop down menu
12 Leave Days Mandatory/ Editable Number of days of leave taken would be selected from drop down menu

There are four templates (general template and three travel templates) per entity for the user to book corporate, project and other expenses against an entity.
1. General Template – to claim re-imbursements for general expenses such as Puja claims etc.
2. Travel template 1– Travel template for overnight stay.
3. Travel template 2– Travel template for non- overnight stay for excess of 12 hours.
4. Travel template 3– Travel template for non- overnight stay for less than, or equal to 12 hours.

1.5 Enter Expense Details

Enter the details of the expenses incurred and the heads under which these have been incurred.
Employee will enter expenses against the following expense types grouped under following expense templates. Justification and receipt is required for all expense types.

1.5.1 Enter ‘Receipt Based Expenses’ Details
This screen allows the employee to enter the details of the receipt based expenses incurred on various days.

Click on Details;

1.5.2 Entering Location Information for the 3 Travel Templates only
Click on the lens symbol against the location field. This is applicable only the three travel templates

Click on GO and select the applicable location

The travel policy has been defined as per the locations.

S No. Field Type Description
1 Start Date Mandatory Date of travel when expense was incurred
2 Receipt Amount Mandatory Amount of expense incurred
3 Expense Type Mandatory Nature of Expense Incurred (Expense types added in Appendix)
4 Justification Mandatory Narration/reason given by employee for the expense incurred. In case of attachments having password, the password has to be entered here.
5 Daily Rate Optional It defaults the receipt amount
6 Number of Days Based on expense type selected If the expense has been incurred for more than 1 day.
7 ‘Attachment Add’ button Optional User has to select Add Attachment button in case any attachments/proofs need to be added.
8 Location field. Mandatory in case of “Hotel Stay Base Rent” or “Travel Ticket expense type” In case the user has selected three travel templates, he needs to select the location from the LOV.
9 Merchant Name Non Mandatory Name of the establishment where expense was incurred
10 Original Receipts Missing Non Mandatory To be checked by employee in case of lost receipt.
11 Receipt Currency Mandatory Two receipts currency would be there; INR and USD . If INR is selected, the exchange rate is defaulted to 1. If USD were selected, then the exchange rate would have to be entered.
12 Employee Number Mandatory Employee number of the person for whom the expense is occured would have to be added for all expense types
13 Check in Time Mandatory This would be present for travel templates only if hotel rent/taxes would be the expense type. The check in timings would have to be entered.
14 Check out Time Mandatory This would be present for travel templates only if hotel rent/taxes would be the expense type. The check out timings would have to be entered.
Click on Continue to navigate through receipt lines;

1.5.3 Location Field- Hotel Stay Base Rent and Travel Ticket Expense Types
Location is mandatory in case of the “Hotel Stay Base Rent” and “Travel Ticket” expense types respectively. For other expense type the location field is not mandatory

For all other expense type, the location field is not mandatory

1.5.4 Attaching receipts to the expense report.
Click on the ADD button to upload an attachment

Click on browse to select the file and click on apply

You will get a confirmation page to show that the attachment has been uploaded

Click on next

1.6 Reviewing you expense report

You can review your final expense report here. If you want to make any changed you would have to click on back to go back to make the changes. Once you click on submit no changes can be made to expense report.

1.6.1 Submitting the expense report

On clicking on the ‘Submit’ button, the expense report is submitted for approval.
A confirmation message is generated giving details of the expense report number, amount as well as the approver to whom the expense claim has been forwarded for approval.

Click ‘Return’ to go back to the Expenses Home Page.
2 Online Tracking of Submitted Expense Report

2.1 Navigation
iExpenses user Responsibility  Expenses Home

2.2 Objective
To enable the employees to know/track the status of their expense claims online.

2.3 Tracking of Submitted Expense Reports
Employees can track the status of their submitted expense reports online by logging into iExpenses responsibility.
This area of the screen shows the expense report which have been Submitted from your end.

S No. Report Status Current Approver Description
1 Pending Manager Approval Approver’s Name is displayed in this column This status shows that the expense report is pending for the supervisor/approver’s action.

2 Pending Payables Approval Accounts Payables This status shows that supervisor/approver has approved the expense report. Employee needs to submit a print of the expense report along with the supporting bills to the finance team for verification.

3 Ready for Payment

None The bills have been verified and approved by the finance department and expense claims will be paid.
4 Paid None The payment to employee has been made and recorded in Oracle.

5 Pending Your Resolution Employee’s Name This status shows that Approver or the finance team has requested for additional information from the employee. The employee should check the notifications and respond to the same.
2.4 ‘Update Expense Reports’ Section

The following statuses of submitted expense reports are available in the ‘Update Expense Reports’ section.

S No. Report Status Description
1 Rejected This status shows that the expense report has been rejected either by the approver or by the finance team. The employee can check the notification to view more details.
2 Withdrawn If an employee withdraws a submitted expense report, it can be viewed and updated from this section. Employee can withdraw the expense report until the time it is not audited by Payables.
3 In Progress

When an employee is in the process of expense report creation, it is saved by the system in this section with a status of ‘In Progress’. This helps the employee to update from the point where it was left the last time.
4 Saved All saved expense reports (saved by the employee during creation process, to be updated later) can be viewed and updated from this section.

2.5 Receipt Status

The receipts status can also be seen in the ‘Tracked Submitted Expense Reports’ Section.

S No. Receipt Status Description
1 Required The employee needs to submit the supporting bills of expenses. This status is also shown when the bills have been submitted but the received date is not entered in the system by the finance team.
2 Not Required The employee need not submit bills for the given expense report, as it contains all such expenses for which receipts are not required by the finance team.
3 Received

When the finance team enters the ‘Receipt Package Received Date’ in the ‘Finance Verification’ responsibility for the given expense claim.
4 Waived In case of missing receipts, if the employees justification on the expenses incurred is found satisfactory, then the finance team can waive of the receipts and approve the claim. In such a case, the employee will view the status of receipts as ‘Waived’.

3 Notifications

3.1 Navigation
iExpenses User Responsibility  Expenses Home

3.2 Objective
To enable the employees to receive notifications in their Oracle logins regarding updates on Expense reports status.

3.3 Notifications
Notifications are sent to the employee as and when an action is taken on a submitted expense report in the following cases.
• Expense report is approved by manager/supervisor.
• Expense report is rejected by manager/supervisor.
• More information is required by manager/supervisor regarding the expense details entered in the expense report.
• Expense report approval is transferred to another approver by the original approver.
• More information is required by finance team regarding the expense details entered in the expense report.
• If amount of expense claim has been reduced by the finance team, based on the details entered and bills provided.

3.4 Resolving ‘Pending Your Resolution’ Claims
Navigate to expenses home page and select the expense claim that needs your resolution

Once selected, add the attachment as required by the same process discussed earlier. Make the necessary changes and submit.

On checking the notification are in your expense home page, a system administrator generated expense claim is displayed

On opening the claim the user has the option to send the additional information required, withdraw the expense report, Reassign the expense report and request information from the auditor.
Click on ‘Send Additional Information’ after adding the attachments
The status in your notification area then changes to ‘Pending Payables Approval’

4 Withdraw a Submitted Expense Report

4.1 Navigation
iExpenses User Responsibility  Expenses Home

4.2 Objective
To enable the employees to withdraw a submitted expense claim in order to make changes and re-submit OR delete the expense claim if submitted wrongly.
4.3 Withdraw Submitted Claims
An employee can withdraw an already submitted expense claim

Click on the withdraw button in front of the claim which has to be withdrawn.

System will ask for a confirmation. Click ‘Yes’.
System gives confirmation message;

The expense report is now shown in the ‘Update Expense Report’ section in ‘Withdrawn’ status.
The user has an option to update this expense by clicking on the ‘Update’ button or ‘Delete’ by clicking on the delete button.

5 Duplicating an Expense Report

5.1 Navigation
iExpenses User Responsibility  Expenses Home
5.2 Objective
To enable the employees to duplicate a submitted expense claim in order to make the required incremental changes to the original report and submit a new claim. This can be used mainly for those employees who claim mobile expenses/ fuel expenses etc month on month.
5.3 Duplicate Submitted Claims
An employee can duplicate an already submitted expense claim. Click on duplicate button.

All the details from the original claim will be copied, the necessary changes can be made, and the claim can be submitted.
6 Assigning someone to file expense claim on your behalf
If the user is planning to go on leave or due to any other reason, he can grant the authority to someone else to file an expense claim on his behalf. On the Expense Report, repsonsibilty home page click on Access Authorizations.

Click on Add Symbol

Enter the name of the Employee and click on GO. Select the employee form the list.

For e.g, you have been authorized to raise expense claims on Mr. XYZ behalf. Then when you click on Create Expense Report, you would get an option to see the list of values in the name of the employee field. You can select XYZ and proceed normally.
Once the expense claim is submitted it would got to Mr. XYZ for approval, then it would go to Mr. XYZ’s manager for approval.

7 Creating expense report via spread sheet.
Oracle also provided the option of creating expense report from spreadsheet. He may use this feature when he is expecting many no of lines in the expense claims.
Click on Import Spreadsheet button.

This window will appear. Click on General/Travel Expense. A prompt would appear to down load an excel file.

Fill the details of the lines as depicted below. You done have to fill any other detail other than the expense lines, and the purpose.

Copy the whole area (CTR A + CTR C) and paste it in the spread sheet import area and click on continue .

Your expense claim would be filled automatically and the user would have to click on Next until the last step.

8 Appendix
Expense Types to choose from in the drop down, while creating expense reports are as follows:
Expense Type Description
Books & Journals Books and journal expenses
Entertainment Expenses Movie tickets, Team Lunch or any other entertainment expenses
External Auditor Food Expenses Food expenses if the employee travels as an external auditor
Filing Fees-ROC-Form 8 & 13 Filing Fees-ROC-Form 8 & 13
Subscription Charges Magazine subscription charges
Relocation-Packing/Loading Relocation-Packing/Loading
Relocation-Labor Charges Labor charges for relocation
Staff Welfare-Spot Gifts Staff Welfare-Gift to employees
Staff Welfare-Helmet Staff Welfare-Helmet charges
Staff Welfare-Raincoat Staff Welfare-Raincoat charges
Staff Welfare-Briefcase Staff Welfare-Briefcase charges
Staff Welfare-Winter wear Staff Welfare-Winter wear charges
Staff Welfare-Uniform Stitching Charges Staff Welfare-Uniform Stitching Charges
Staff Welfare-Uniform Material Charges Staff Welfare-Uniform Material Charges
Staff Welfare-Cheppals Slippers for field staff
Staff Welfare-Health Check Up Health check-up charges
Staff Welfare-Food Expenses Food expenses charges
Staff Welfare-Medical Re Imbursements Medical claim
General-Others General charges

—————————– End Of Document —————————–

Reply
uppi says January 12, 2015

create or replace PACKAGE BODY XXSFL_AR_RCPT_FP_VAL_PKG
IS
— +=========================================================================================+ —
— +=========================================================================================+ —
— |$Id: XXSFL_AR_FORM_PERSONAL_PKG | —
— | | —
— |Description : Package For Forms Personalisation Validations on AR- Receipts System | —
— | | —
— |This package contains the following sub programs: | —
— |================================================= | —
— | | —
— |Type Name Description | —
— |========= =========== =================================== | —
— |FUNCTION rcpts_validation Function To Validate If records are Locked by PS System | —
— |FUNCTION cheque_rtn_validation Function To Validate Cheque Returns | —
— |FUNCTION rcpts_dist_line_val Function To Validate Receipts Distribution Lines | —
— |FUNCTION XXSFLAR_BANKINFO_VALIDATION Function To Validate Bank Information | —
— | | —
— | | —
— |Change Record: | —
— |=============== | —
— |Version Date Author Remarks | —
— |======== =========== =============== =========================================================| —
— |Draft1a 24-MAR-2014 santhanagopalan Initial draft version | —
— +====================================================================================================+ —

FUNCTION rcpts_validation (p_cash_receipt_id IN NUMBER) RETURN VARCHAR2
IS

lc_flag VARCHAR2(10);
lc_pros_ind VARCHAR2(10);
lc_challan VARCHAR2(200);

CURSOR lcu_receipt_record
IS
SELECT XXARDS.ps_to_of_trf_ind FLAG
,XXARDS.pros_ind PROS_IND
,XXARDS.challan_number CHALLAN
FROM xxsfl_Ar_rct_dtls_stg XXARDS
WHERE XXARDS.cash_receipt_id = p_cash_receipt_id;

BEGIN
lc_flag:= NULL;
lc_pros_ind := NULL;
lc_challan := NULL;

FOR lr_receipt_record IN lcu_receipt_record
LOOP
lc_flag := lr_receipt_record.FLAG;
lc_pros_ind := lr_receipt_record.PROS_IND;
lc_challan := lr_receipt_record.CHALLAN;
END LOOP;

IF (lc_pros_ind = ‘Y’ AND lc_challan IS NULL) THEN
RETURN(‘Y’);
ELSE
RETURN (‘N’);
END IF;

EXCEPTION
WHEN OTHERS THEN
RETURN(‘N’);
END;

— Function used to populate the Cheque return indicator error message
FUNCTION cheque_rtn_validation (p_chq_rtn_ind_old IN VARCHAR2,p_chq_rtn_ind_new IN VARCHAR2) RETURN NUMBER
IS
ln_check NUMBER;
BEGIN

IF ((p_chq_rtn_ind_old= ‘Y’) AND (p_chq_rtn_ind_new IS NULL)) THEN
RETURN(1);
ELSE
RETURN (0);
END IF;

EXCEPTION
WHEN OTHERS THEN
RETURN(NULL);
END;

— Function used to Validate the Distribution line details
FUNCTION rcpts_dist_line_val (p_cash_receipt_id IN NUMBER) RETURN NUMBER

IS

ln_counter NUMBER;

CURSOR lcu_rcpt_dist
IS
SELECT COUNT(1) COUNTER
FROM ar_misc_cash_distributions_all
WHERE cash_receipt_id = p_cash_receipt_id;

BEGIN

FOR lr_rcpt_dist IN lcu_rcpt_dist
LOOP
ln_counter := lr_rcpt_dist.COUNTER;
END LOOP;
RETURN (ln_counter);

EXCEPTION
WHEN OTHERS THEN
RETURN(0);

END;

FUNCTION XXSFLAR_BANKINFO_VALIDATION (p_cheque_no IN VARCHAR2,p_bank_name IN VARCHAR2,p_bank_loc IN VARCHAR2) RETURN NUMBER
IS
ln_counter NUMBER;

CURSOR lcu_bank_record
IS
SELECT count(1) COUNTER
FROM Ar_cash_receipts_all ACRA
WHERE ACRA.Attribute1 = p_cheque_no
AND ACRA.Attribute2 = P_bank_name
AND ACRA.Attribute3 = p_bank_loc;

BEGIN

FOR lr_bank_record IN lcu_bank_record
LOOP
ln_counter := lr_bank_record.counter;
END LOOP;
RETURN (ln_counter);
EXCEPTION
WHEN OTHERS THEN
RETURN(0);
END;

FUNCTION XXSFLAR_RCPTMET_VALIDATION (P_RCPT_MTD_ID IN NUMBER,P_ATT_CAT IN VARCHAR2,P_ACT_TYPE IN VARCHAR2) RETURN NUMBER
IS
ln_count NUMBER;
lc_rcpt_method VARCHAR2(100);

CURSOR lcu_val_rc_mtd (P_Method_Id IN NUMBER)
IS
SELECT ARRM.Printed_Name NAME
FROM AR_RECEIPT_METHODS ARRM
WHERE ARRM.Receipt_Method_Id = P_Method_Id;

BEGIN
FOR lr_val_rc_mtd IN lcu_val_rc_mtd(P_RCPT_MTD_ID)
LOOP
lc_rcpt_method :=lr_val_rc_mtd.NAME;
END LOOP;
ln_count := 0;
IF ((lc_rcpt_method =’NEFT’ OR lc_rcpt_method =’RTGS’) AND (P_ATT_CAT = ‘SF Cheque Receipt’ OR P_ATT_CAT = ‘SF Cash Receipt’))THEN
ln_count := 1;
ELSIF ((lc_rcpt_method =’DD’ OR lc_rcpt_method =’Out Station Cheque’ OR lc_rcpt_method =’Local Cheque’ ) AND (P_ATT_CAT = ‘SF NEFT/RTGS Receipt’ OR P_ATT_CAT = ‘SF Cash Receipt’ ) )THEN
ln_count := 1;
ELSIF (lc_rcpt_method =’Cash’ AND (P_ATT_CAT = ‘SF Cheque Receipt’ OR P_ATT_CAT = ‘SF NEFT/RTGS Receipt’)) THEN
ln_count := 1;
ELSIF ((lc_rcpt_method =’NEFT’ OR lc_rcpt_method =’RTGS’ OR lc_rcpt_method =’Cash’ ) AND P_ACT_TYPE = ‘Deposit’) THEN
ln_count :=2;
ELSE
ln_count := 0;
END IF;
RETURN (ln_count);
EXCEPTION
WHEN OTHERS THEN
RETURN(0);
END;

— Function To Validate The Post Mark Date Is Entered Or Not

FUNCTION XXSFLAR_POSTMARK_VALIDATION (P_RCPT_MTD_ID IN NUMBER,P_POST_DATE IN DATE) RETURN NUMBER
IS
ln_count NUMBER;
lc_rcpt_method VARCHAR2(200);

CURSOR lcu_val_rc_mtd (P_Method_Id IN NUMBER)
IS
SELECT ARRM.Printed_Name NAME
FROM AR_RECEIPT_METHODS ARRM
WHERE ARRM.Receipt_Method_Id = P_Method_Id;

BEGIN
FOR lr_val_rc_mtd IN lcu_val_rc_mtd(P_RCPT_MTD_ID)
LOOP
lc_rcpt_method :=lr_val_rc_mtd.NAME;
END LOOP;
ln_count := 0;
IF ((lc_rcpt_method =’DD’ OR lc_rcpt_method =’Out Station Cheque’ OR lc_rcpt_method =’Local Cheque’ ) AND (P_POST_DATE IS NULL ))THEN
ln_count := 1;
ELSIF (P_POST_DATE SYSDATE ) THEN
ln_count := 1;
ELSE
ln_count := 0;
END IF;
RETURN (ln_count);
EXCEPTION
WHEN OTHERS THEN
RETURN(0);
END; — END OF Function

FUNCTION XXSFLAR_BADV_DATE_VALIDATION (P_RCPT_MTD_ID IN NUMBER) RETURN NUMBER
IS
ln_count NUMBER;
lc_rcpt_method VARCHAR2(100);

CURSOR lcu_val_rc_mtd (P_Method_Id IN NUMBER)
IS
SELECT ARRM.Printed_Name NAME
FROM AR_RECEIPT_METHODS ARRM
WHERE ARRM.Receipt_Method_Id = P_Method_Id;

BEGIN
FOR lr_val_rc_mtd IN lcu_val_rc_mtd(P_RCPT_MTD_ID)
LOOP
lc_rcpt_method :=lr_val_rc_mtd.NAME;
END LOOP;
ln_count := NULL;
IF ((lc_rcpt_method =’DD’ OR lc_rcpt_method =’Out Station Cheque’ OR lc_rcpt_method =’Local Cheque’ ) )THEN
ln_count := 0;
ELSE
ln_count := 1;
END IF;
RETURN (ln_count);
EXCEPTION
WHEN OTHERS THEN
RETURN(1);
END;

END XXSFL_AR_RCPT_FP_VAL_PKG;

Reply
Add Your Reply