Saturday 26 February 2022

Creating NACHA payments from Oracle

Original Source ref# :

https://docs.rackspace.com/blog/creating-nacha-payments-from-oracle/

Creating NACHA payments from Oracle

Vinit Bhatnagar | January 6, 2021

Oracle / Database

The National Automated Clearing House Association (NACHA) file format is one of the most common electronic payment file types. It is used for executing domestic ACH payments through the Automated Clearing House Network.

Overview

A NACHA file is a payment instruction file sent or uploaded to a bank portal to execute a mass payment batch. This file is formatted according to NACHA specifications and varies by bank. Many banks that support the NACHA file, including Wells Fargo®, Bank of America®, J.P. Morgan Chase®, T.D. Bank N.A.®, and so on.

Oracle® R12 provides the following standard formats:

US NACHA PPD: According to Treasury Software®: “Prearranged Payment and Deposit - Used to pay or collect from personal (consumer) accounts. Examples include direct deposit of payroll to employees, payments to individuals, and collections from personal (consumer) clients.”

US NACHA CCD: The term CCD, in banking, refers to cash concentration and disbursement for corporate credits and debits.

Now, let’s set up a format in Oracle and generate a sample NACHA file.

Set up format

Use the following steps to set up the format:

1. XML TEMPLATES

Create an XML publisher template in this step by using the seeded US NACHA CCD Format provided by Oracle.

Navigate to XML Publisher Responsibility > Templates > Create template.

Use the following settings:

Data Definition: Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
Type: eText – Outbound
File: IBYDE_S103_en.rtf This is a standard template by Oracle. You can take this as a base and modify it as per your bank or business requirements.

2. PAYMENT FORMATS

Navigate to Payables Manager: Setup > Payments > Payments Administrator > Formats > Formats.

Use the following settings:

Data Extract: Oracle Payments Funds Disbursement Payment
Instruction Extract: Version 1.0
XML Publisher Template: US NACHA CCD Format

3. PAYMENT DOCUMENTS

Assume that you have already created your Banks, Bank Branches, and Bank Account. In this step, you attach the Payment Format US NACHA CCD Format to the Payment Document.

Navigate to Payables Manager > Setup > Payment > Bank Accounts and click Manage Payment Documents and Create.

Use the following settings:

Name: XXXX NACHA
Paper Stock Type: Blank Stock
Format: US NACHA CCD Format
First Available Document Number: 100001
Last Available Document Number: 999999

Click Apply.

4. PAYMENT PROCESS PROFILE SETUP

Navigate to Payables Manager > Setup > Payment > Payment Administrator > Payment Process Profiles and click Create.

Use the following settings:

Code: XXXX_PPP
Processing Type: Electronic
Name: XXXX PPP
Payment Instruction Format: US NACHA CCD Format

Click Apply.

Generate the NACHA file

Use the following steps to generate the NACHA file:

1. ENTER INVOICE FOR A SUPPLIER

Navigate to Payables Manager > Invoices > Entry > Invoices.

Ensure that the supplier has the payment method as Electronic, and the invoice is in Validated Status.

2. PAY THE INVOICE

Navigate to Payables Manager > Payments > Entry > Payments Manager.

Perform the following steps:

Enter the PPR name.
Enter the Payee name if you want to limit the invoice selections in this PPR.
Select Electronic payment method.

Enter your bank account, payment document, and PPP. Then click Submit.

Refresh the status and wait till the PPR status is Confirmed.
Navigate to View > Requests > Find.

Find Format Payment Instructions with text output and click View output.

The output from Format Payment Instructions is the NACHA file generated from the preceding invoice and its payment. You can save this to your local desktop and upload it to the bank for further processing.

Conclusion

Generating and uploading a NACHA file on the bank’s server is becoming one of the most demanding features across industries. Across all ERPs, Oracle provides one of the best processes for generating the NACHA file. After a few setup steps, you are ready to go with your ACH file. Different banks have different formats of NACHA, so Oracle also provides the option of customizing the standard RTF file to match the individual bank specification

Tuesday 28 December 2021

Ffile transfer Sell script , FTP & SFTP

SFTP Script:-

sftp_error=$(
#!/bin/bash
HOST=10.94.146.17
USER=tboecrm
PASSWORD=****
sftp -inv $HOST <<EOF
user $USER $PASSWORD
cd /home/tboecrm/testing
mput ${FILE_NAME1}
bye
EOF
)

----------------------------------------------

Copy File Shell Script

Objectives:

Steps to Register Shell Script as a concurrent program

Sample Shell Script to copy the file from source to destination

Basic Shell Script Commands

Steps to Register Shell Script as a concurrent program

step 1:

=======

Place the .prog script under the bin directory for your

applications top directory.

For example, call the script XXFTP.prog and place it under

$CUSTOM_TOP/bin

step 2:

=======

Make a symbolic link from your script to $FND_TOP/bin/fndcpesr

For example, if the script is called XXFTP.prog use this:

ln -s $FND_TOP/bin/fndcpesr XXFTP

This link should be named the same as your script without the

.prog extension.

Put the link for your script in the same directory where the

script is located.

step 3:

=======

‘Host'. Use the name of your script without the .prog extension

as the name of the executable.

For the example above:

Use ERPS_DEMO

step 4:

=======

Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd

$2 = userid(apps)

$3 = username,

$4 = request_id

Any other parameters you define will be passed in as $5 and higher.

Make sure your script returns an exit status also.

Sample Shell Script to copy the file from source to destination

#Note: If you see # in front of any line it means that it's a comment line not the actual code

#** ********************************************************************

# Created By : Rohidas D

# Creation Date : 21-Dec-2021

# Script Name : XXFTP.prog

# Description : This Script accepts three parameters

# 1)Data File Name 2)Source Directory Path 3)Target Directory Path

# Then copy the file from source location to target location.

# If copy fails send the error status/message to concurrent program so that user can see status.

# ========

# History

# ========

# Version #

#** ********************************************************************

#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters

# $1 : username/password of the database

# $2 : userid

# $3 : USERNAME

# $4 : Concurrent Request ID

DataFileName=$5

SourceDirectory=$6

TargetDirectory=$7

echo "————————————————–"

echo "Parameters received from concurrent program .."

echo " Time : "`date`

echo "————————————————–"

echo "Arguments : "

echo " Data File Name : "${DataFileName}

echo " SourceDirectory : "${SourceDirectory}

echo " TargetDirectory : "${TargetDirectory}

echo "————————————————–"

echo " Copying the file from source directory to target directory…"

cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}

if [ $? -ne 0 ]

# the $? will contain the result of previously executed statement.

#It will be 0 if success and 1 if fail in many cases

# -ne represents not "equal to"

then

echo "Entered Exception"

exit 1

# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success

else

echo "File Successfully copied from source to destination"

exit 0

echo "****************************************************************"

----------------------------------------------------------------

Basic Shell Script Commands

# Create Directory
mkdir
# Remove Directory
rmdir
#remove folder with files
rm -r -f
# Change Directory
cd
# Create new file
vi
#insert data into file
vi
esc i
#Save file
esc :wq enter
# exit without saving changes
esc :q! enter
# open existing file
vi
#remove file
rm
# copy file with same name
cp /
# copy file with new name
cp / /
# Move file with same name
mv /
# move file with data appended to filename in the front
mv / /`date+%H%M%d%m%y`
#print line
echo “your text here to print”
#print date
echo `date`

FNDLOAD Commands

To Download/Upload FND_LOOKUPS:

FNDLOAD apps/Emzci9#b 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct xxaoa_file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="HUTCH" LOOKUP_TYPE="XXVFENT_FNDD_LOOKIUPTEST"

FNDLOAD apps/Emzci9#b O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct xxaoa_file_name.ldt

---------------------------------------------------------------

Concurrent Program

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XXCUST_CP"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Lookups

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXCUST_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XXCUST_LKP"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXCUST_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Message

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="XXCUST_MESG%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Request Set and Link

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_RS.ldt REQ_SET REQUEST_SET_NAME='XXCUST_RS'

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='XXCUST_LNK'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_LNK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Form Function

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt FUNCTION FUNC_APP_SHORT_NAME='XXCUST' FUNCTION_NAME='XXCUST_FUNC'

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt

Profile

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_PROF.ldt PROFILE PROFILE_NAME="XXCUST_PROFILE" APPLICATION_SHORT_NAME="XXCUST"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_FUNC.ldt

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt MENU MENU_NAME="XXCUST_MENU"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt

Data Definition

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXCUST_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXCUST DATA_SOURCE_CODE=XXCUST_DS

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_DD.ldt

Wednesday 15 December 2021

Oracle R12 Multi ORG (MOAC) COncept

The multiorg architecture is meant to allow multiple companies or subsidiaries to store their records within a single database. The multiple Organization architecture allows this by partitioning data through views in the APPS schema. Multiorg also allows you to maintain multiple setsof books. Implementation of multiorg generally includes defining more than one Business Group.

Basically the different entities in multi-org are:
• Business Group (BG)
• Sets of Books (SOB)
• Legal entities (LE)
• Operating units (OU)
• Inventory organizations (IO)

The difference in multi-org in 11i & R12i is "MOAC" concept(Multi-Org Access Control) is
"MOAC provided the role based access, shared services, to
perform multiple tasks across different operation units from
within single application responsibility."

It is controlled by MO: Security profile.

R12 initial Setup Reference:

http://oracleebspro.blogspot.com/2016/08/multi-org-concept-in-oracle-apps-r12.html

How to set org context in Oracle Apps R12 in back-end in case of multiple org-id?

Set Multi org/Single org operating unit form plsql:

1-exec MO_GLOBAL.INIT('Give Application name');

To set Multi Org unit:
2- exec MO_GLOBAL.INIT('M');

To set Single  Org unit:
3- exec MO_GLOBAL.INIT('S');

Explanation: mo_global.init checks the application_short_name parameter against table fnd_mo_product_init and decides by the table's status column to proceed further with multiple or single org logic.

If the application_short_name parameter passed is not registered in that table, mo_global.init will fail. Oracle added the dummy short names 'M' and 'S' to fnd_mo_product_init to allow calling the procedure without a real apps name.


-Initialization of view
R12--> mo_global.set_policy_context(S,org_id) ;

11i -> dbms_application_info.set_client_info(:org_id);
If we know the user id, rep id, and app_resp_id then we can use the below query
fnd_global.apps_initialize(:p_user_id, :p_resp_id, :p_app_resp_id);