Visitors Adda

CRED Referal Link, Credit Card Payment Offers and Rewards

2019-08-01T13:13:00.001+05:30

Hi All,

you can get offers and rewards and Cash Backs for Credit Card Payments through CRED App.

You can also get 150 rs offer when you install the app by using below link.
https://cred.onelink.me/spQx/a481d8e8

or type that url in your mobile browser.

you have been referred to CRED, an app for India's creditworthy. make your credit card bill payment and win rewards from Uber, Amazon, Flipkart and more. #whatsyourcred
https://cred.onelink.me/spQx/a481d8e8

HariHareswar Temple and Beach

2015-12-08T08:05:00.001+05:30

Uber Cabs Free Rides

2015-08-28T15:27:00.000+05:30

Hi,

At the time of registration on Uber, just apply below promo code then you will get Free Ride.

JAGADEKARARUE

You can refer the same code to your friends also, so that they also can get their free rides.

Enjoy the journey.

Tags: Uber Coupon Code, Uber cab Promo Code, Uber Promocode, free ride in Uber cabs, How to book Uber cab free,How to go in Uber free, how to get free ride in uber.

Ola Cabs Free Rides

Hi All,

Like Uber we have chance to get free rides in OLA cabs also. for that just enter below codes while registering into OLA App.

HJV96J
(or)
VY43JV

Tags: Ola cabs free ride, OLA Cabs, Ola Promocodes, Ola Promo Codes, how to get free ride in OLA, how to book ola cab free, how to go in ola for free.

Beautiful Butterfly

2014-10-16T19:48:00.001+05:30

Beautiful Butterfly photo taken at Synechron Pune.

My New House

2014-10-10T19:18:00.000+05:30

We constructed this house on 21st Aug 2014

Synechron Annual Day celebrations With my Room Mates in 2014

2014-03-07T19:49:00.000+05:30

One of the memorable day in my life... Thanks to Shripad (My colleague).

I attended the Synechron Annual Day Party with my Room Mates on 01st March 2014 in Pune.

Kasanur Vinayakudu

2013-09-18T14:44:00.000+05:30

This is Kasanur Ganapathi in 2013 september.

Last two pics are my drawings.

Rainbow

2013-09-18T13:32:00.001+05:30

Hi this is called Rainbow when rain going to stop that time we can see this. This has 7 colours. In telugu it is called as Indhra Dhanussu. So people thought that when Rainbow genarated then rain will stop.

Bhanukota Photos

2013-09-18T12:57:00.003+05:30

These Photos are taken at Bhanukota on 9th Sep 2013 which is Vinayaka Festival Day.

Today we made Abhishekam to lord Shiva with my family.

Oracle Apps Frequent Errors and Solutions

2013-06-18T16:20:00.001+05:30

10-may-2013

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

1) Error in Report while generating .XMl file.

REP-0069: Internal error

REP-57054: In-process job terminated: Terminated with error:

REP-1401: '': Fatal PL/SQL error occurred.

Solution

---------

1) It shows this error because in formula column I wrote end if before RETURN clause.

So i wrote end if after the return clause then it will compile successfully

13-may-2013

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

2) Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number

This was occurred when I click the preview in rtf.

Solution

---------

2) Used to_number (String)

3) Routine FDPREP cannot read the file /oraapp/ERP/apps/apps_st/appl/ar/12.0.0/reports/US/ XXST_AR_INV_INTER.rep.

Check if the file exists under correct product directory.

Solution

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

3) In above error observe carefully there is a space in front of .rep file name. Means in executable remove space and

Save

4) Enter Password:

REP-0004: Warning: Unable to open user preference file.

REP-1316: After Report Trigger references column 'inv_max' at wrong frequency.

REP-0069: Internal error

REP-57054: In-process job terminated: Terminated with error:

REP-1316: After Report Trigger references column 'inv_max' at wrong frequency.

Solution

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

4) Here inv_max is in group in RDF data model but I took this field or formula column or summary column in after report trigger

So this error occurs what I do is I created that field (summary column) in report level not group level so it was rectified.

07-Jun-2013

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

5) Enter Password:

REP-0004: Warning: Unable to open user preference file.

REP-1401: 'cf_vend_nameformula': Fatal PL/SQL error occurred.

ORA-06502: PL/SQL: numeric or value error

REP-0069: Internal error

REP-57054: In-process job terminated: Terminated with error:

REP-1401: 'cf_vend_nameformula': Fatal PL/SQL error occurred.

ORA-06502: PL/SQL: numeric or value error

Solution

----------

5) This error occurred because that formula column has a number data type but it returns varchar values

So I changed data type as char and size 100 then submit report works fine.

07-06-2013

----------

6) Enter Password:

REP-0004: Warning: Unable to open user preference file.

REP-1416: 'beforereport': User exit 'FND'. IAF GET: unknown column 'P_CONC_REQUEST_ID'.

REP-0069: Internal error

REP-57054: In-process job terminated: Terminated with error:

REP-1416: 'beforereport': User exit 'FND'. IAF GET: unknown column 'P_CONC_REQUEST_ID'.

Solution

---------

6) Whenever we use user exits in report without creating P_CONC_REQUEST_ID parameter then this error will occur.

Action

------

To achieve this error you should create P_CONC_REQUEST_ID in user parameters of your report.

19-Jun-2013

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

Error

--------

REP-0091: Invalid value for parameter 'P_VENDOR_NAME'.

Solution

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

This error occurred because in rdf P_VENDOR_NAME parameter having only 40 size but value maximum is 58 so it occurred.

Action

---------

Increase that width to 100 then it will solve

FRM-41072: cannot create group FRM-41827: Group Does not exist.

2013-05-23T19:40:00.002+05:30

Hi for below error

FRM-41072: cannot create group <Group Name>
FRM-41827: Group Does not exist.

Solution
----------------
If it will occur at the time of changing or restricting LOV then follow below procedure.

First open the form and place cursor on field which have list of values in drop down

then go to help diagnostics examine
give system
current block

then it shows block name this was the Data Block Name in our .fmb file.

in same form go to help--> About oracle applications

here we can see the form name. i.e our .fmb name it should be in AU_TOP

so get that .fmb and open through Form Builder

here we can click on our Data block then click on Items

select our field which is having list of values then press F4

here we can see the LOV name.

then go to LOV in the same form builder down

then right click on our LOV here copy the query and paste it into TOAD or Sql Developer

Made changes in where condition what ever we require. Dont change columns and names.

Then go to frontend form and help-->diagnistics-->custom code --> form personalize

here give sequence and name and function

below select WHEN NEW FORM INSTANCE

then click on actions

10 Builtin    All Create Record Group from query:
paste your query here. and in the group name give some name i.e. XX_GRP.( As your like)

20 Property         All                                                Object Type - LOV
                                                                                Target Object - select your LOV name here
                                                                                 Property Name - GROUP_NAME
                                                                        Value - give the new record group name here i.e. XX_GRP.

By doing above procedure as it is sometimes getting error so what we will do that time is

just done all changes and save and validate and validate all dont click on Apply Now.

now close the form and re open now we can see restricted lov.

Hope this helps.

Thanks,
Jagadekara Reddy.

Cannot convert to number error in RTF

2013-05-13T19:30:00.003+05:30

In Rtf if we are using any function at that time if you get
'Cannot convert to number error in RTF " error then check that function carefully.

So Use to_number(String) then it will resolved.

Buttler Telugu

2013-05-13T19:11:00.000+05:30

hi Viewers Happy to see my post. Here I invented the new funny language named Buttler Telugu.
This is very funny language. Usually I chat with my friends with this funny Buttler Telugu So all my friends are enjoyed alot. here I mentioned below some of buttler telugu words. So start using buttler Telugu and make others joy.

English                                                           Buttler telugu
------------                                                   -------------------
Big Bazar(Shopping)                                Pedda Bazaar (ఫెద్ద బజార్ )
TV9    TV Thommidi(టీవీ తొమ్మిది )
He is Talking                                             Talkutunnadu(టాకుతున్నాడు )
Going To Office                                         Karyalam Going(కార్యాలయం గోయింగ్ )
He is Speaking                                           Speakutunnadu(స్పీకుతున్నాడు )
Face Book                                                Moham book(మొహం బుక్ )
Pinged in Facebook                                    Pinginaadu(పింగినాడు)
Shared In Face Book                                 Sharnaadu(శేరినాడు)
Worker Work Manishi(వర్క్ మనిషి )
Two Minutes    Rendu Minutes/Twi Nimishalu(రెండు మినిట్స్ / టూ నిమిషాలు )
Chief Minister    Chief Manthri(చీఫ్ మంత్రీ )
Which Movie                                            which Cinema(విచ్ సినిమా )
Six Runs                                                    Six Parugulu(సిక్స్ పరుగులు )
Rice Cooker                                                Annam Cooker(అన్నం కుక్కర్ )
Two Kgs Rendu Kgs(రెండు కేజిస్ )
Finger Prints                                              Finger Mudralu(ఫింగర్ ముద్రలు )
He Ate                                                       Yetinadu(ఈటినాడు )
He was Drunked                                          Drinkinaadu(డ్ర్రిన్కినాడు )
News Paper                                               News pathrika(న్యూస్ పత్రిక )
He taked                                                    Takinaadu(టేకినాడు )
Five Crores                                               Five Kotlu(ఫైవ్ కోట్లు )
Foreign Country                                       Foreign Desham(ఫారిన్ దేశం )
Software Engineer                                   Methaware Engineer (మెత్త వేర్ ఇంజనీర్ )
Half An Hour                                          Ardha Hour (అర్ధ అవర్ )
First Floor                                               Modati Floor (మొదటి ఫ్లోర్ )
Hard Disk Gatti Disk (గట్టి డిస్క్ )
Happy Birth Day Happy Birth Roju (హాపీ బర్త్ రోజు )
Week End                                               Vaaram End (వారం ఎండ్ )
Sweet Memories Tiyya Memories (తియ్య మెమోరీస్ )
Right Click                                             Kudi Click (కుడి క్లిక్ )
Nail Cutter                                             Gorla Cutter (గోర్ల కట్టర్ )
Body Pains    Sareeram Pains (శరీరం పెయిన్స్ )
Good Day Manchi Day (మంచి డే )
T- Shirt T Chokka (టీ చొక్కా )
Windows 7 OS    Kitikilu 7 OS (కిటికీలు సెవెన్ వో ఎస్ )
Child Dog Dog Pilla (డాగ్ పిల్ల )
Black Mail    Nalla Mail (నల్ల మెయిల్ )
Black Money                                        Nalla Money(నల్ల మనీ )
Mango fruit                                           Mamidi fruit(మామిడి ఫ్రూట్ )
One day match                                       Oka Day Match(ఒక డే మ్యాచ్ )
Seetamma Vaakitlo Sirimalle Chettu Seetamma Door Lo Siri Malle tree(సీతమ్మ డోర్ లో సిరి మల్లె త్రీ )
Iddaru Ammayilatho Iddaru Girls tho (ఇద్దరు గర్ల్స్ తో )
SaniwarPet Saturdaypet (సాటర్డే పేట్ )
Coconut Oil Coconut Nune(కోకోనట్ నునె )
Prathama Kumarudu Prathama Son (ప్రథమ సన్ )

Like this Daily use Above words and see the different nature.
I Hope In future This will use in movies also for comedy seens.

                                      All The Best.

------>Jగన్ /జగదేKar.

2013-05-11T11:06:00.002+05:30

REP-1219 <object name> has no size - length or width is zero
 
Solution
--------
Error REP-1219 is usually caused when a report is designed as a bitmap, but 
then is run under character mode.  The character cell boundary falls
midway down the boilerplate object border.  Hence, when Reports tries to map 
the object to a character cell, both the top and bottom of the object are 
mapped to the top of the cell.  
 
If you are using a standard report, convert the .rdf file to .rdf with 
dunits=character.  For example:
 
   r25convm userid=apps/apps stype=rdffile source=whatever.rdf dtype=rdffile
   dunits=character dest=whatever.rdf overwrite=yes batch=yes

Pune Mehathoba Temple

2013-05-07T22:23:00.003+05:30

My Pune Pics

2013-05-07T21:34:00.002+05:30

Performance tuning Steps

2013-05-04T20:34:00.003+05:30

Steps to Tune the Query :
---------------------------------
1.Make sure the proper joins for whatever tables in from clause.
2.Make the joins with "ID's" instead of Database columns.
3.Avoid Multitable views, Use Org Specific views whereever possible.
4.If the custom table is having large amount of create indexes.
5.Avoid Type casting and Aggregate functions,Negation operators
on Indexed columns.
6.Instead of using the whole query in a single cursor or group ,
split into multiple Groups and link them.
7. use union all instead of Union
8. Use Exists inplace of in
9. Avoid usage of Negation operators like NOT, !=.
10.Use Union instead of OR Clause.
11.Avoid Usage of outer joins , use Equi joins whereever possible
12.Use Where Clause instead of Having Clause.

Customer Interface Script In Oracle Apps

2013-02-26T22:52:00.003+05:30

CREATE OR REPLACE PACKAGE BODY xx_arcnv01_customer_load_pkg
AS
/*
-------------------------------------------------------------
Package Name : XX_ARCNV01_CUSTOMER_LOAD_PKG
Author's Name : Jagadekar
Date Written : 19-Dec-2006
RICEW Object id : AR_CNV_01
Purpose : Package Body
Program Style :
Maintenance History:
Date: Name Version Remarks
----------- ------------- ----------- ------------------
19-Dec-2006 Jagadekar 0.1 Initial Version
05-Feb-2007 Jagadekar 1.0 Changes made as per the changes recomended
--------------------------------------------------------------
*/
/*-----------------------------------------------------------

Purpose-Procedure to Process Customer record
--
-------------------------------------------------------------*/
PROCEDURE process_customer_record (
p_customer_process IN xx_ar_cust_preint%ROWTYPE,
p_success OUT VARCHAR2,
p_error_api OUT VARCHAR2,
p_message_count OUT NUMBER,
p_message_data OUT VARCHAR2
);

/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose-Procedure to Update Custom Tables
--
-------------------------------------------------------------*/
PROCEDURE update_custom_table (
p_customer_id IN xx_ar_cust_stg.customer_id%TYPE,
p_req_id IN NUMBER,
p_status IN VARCHAR2,
p_table_type IN VARCHAR2,
p_success OUT VARCHAR2,
p_message_data OUT VARCHAR2
);

/* Cursor declare section*/
--Cursor to select from Staging table xx_ar_cust_stg to check customer_type is ORG/PERSON
CURSOR c_cust_control
IS
SELECT *
FROM xx_ar_cust_stg
WHERE status = 'NW';

--Cursor to select from Staging table xx_ar_cust_stg to move into preinterface table
CURSOR c_cust_preint
IS
SELECT *
FROM xx_ar_cust_stg
WHERE status = 'NW';

--Cursor to select from Staging table xx_ar_address_stg to move into preinterface table
CURSOR c_address_preint
IS
SELECT *
FROM xx_ar_address_stg
WHERE status = 'NW';

--Cursor to select from Staging table xx_ar_contact_stg to move into preinterface table
CURSOR c_contact_preint
IS
SELECT *
FROM xx_ar_contact_stg
WHERE status = 'NW';

--Cursor to select from Staging table xx_ar_cust_stg to validate customer_type ORG
CURSOR c_validate_org
IS
SELECT *
FROM xx_ar_cust_stg
WHERE status = 'NW' AND customer_type = 'ORG';

--Cursor to select from Staging table xx_ar_cust_stg to validate customer_type PERSON
CURSOR c_validate_person
IS
SELECT *
FROM xx_ar_cust_stg
WHERE status = 'NW' AND customer_type = 'PERSON';

--Cursor to select from Pre-Interface table xx_ar_cust_preint for customer_type ORG
CURSOR c_org_process
IS
SELECT *
FROM xx_ar_cust_preint
WHERE status = 'IP' AND customer_type = 'ORG';

--Cursor to select from Pre-Interface table xx_ar_cust_preint for customer_type PERSON
CURSOR c_person_process
IS
SELECT *
FROM xx_ar_cust_preint
WHERE status = 'IP' AND customer_type = 'PERSON';

--Cursor to select from Pre-Interface table xx_ar_contact_preint
CURSOR c_contacts (
p_cust_id xx_ar_contact_preint.customer_id%TYPE,
p_addr_id xx_ar_address_preint.address_id%TYPE
)
IS
SELECT *
FROM xx_ar_contact_preint
WHERE customer_id = p_cust_id AND address_id = p_addr_id;

--Cursor to select from Pre-Interface table xx_ar_address_preint
CURSOR c_address (p_cust_id xx_ar_address_preint.customer_id%TYPE)
IS
SELECT *
FROM xx_ar_address_preint
WHERE customer_id = p_cust_id;

/*--------------------------------------------------------------------------------------------------------
Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/
--General Variables
l_error_api VARCHAR2 (200);
l_msg_count VARCHAR2 (2);
l_msg_data VARCHAR2 (300);
l_success VARCHAR2 (1);
l_completed BOOLEAN;
l_process_status NUMBER;
l_process_id NUMBER;
l_count NUMBER := 0;
l_cust_count NUMBER := 0;
l_person_error NUMBER := 0;
l_org_error NUMBER := 0;
l_cnt_org NUMBER := 0;
l_cnt_profile NUMBER := 0;
l_rec_insert_cnt NUMBER := 0;
l_cust_cnt NUMBER := 0;
l_addr_cnt NUMBER := 0;
l_contact_cnt NUMBER := 0;
l_success_pr NUMBER := 0;
l_set_process_count NUMBER := 0;
l_file_record_count NUMBER := 0;
--EMF Variables
l_package_name xx_emf_message_headers.program_name%TYPE
:= 'XX_ARCNV01_CUSTOMER_LOAD_PKG';
l_program_name xx_emf_message_headers.program_name%TYPE
:= l_package_name || '.' || 'MAIN';
l_ricewid xx_emf_message_headers.ricew_id%TYPE
:= 'AR_CNV_01';
--Object Name
l_request_id xx_emf_message_headers.request_id%TYPE;
l_error_rec xx_emf_message_details%ROWTYPE;
--used for EMF error header
l_messages_rec xx_emf_message_details%ROWTYPE;
--used for EMF messages
l_null_rec xx_emf_message_details%ROWTYPE := NULL;
--used for EMF messages
l_header_id xx_emf_message_headers.header_id%TYPE;
--EMF ID
l_return_value NUMBER := NULL;
--stores number returned by EMF API's
l_err_width_rec xx_emf.output_rec_type;
--record type variable for assigning width to error section
l_processed_recs NUMBER := 0;
--stores total records for summary report
l_successful_recs NUMBER := 0;
--stores total successful records for summary report
l_error_recs NUMBER := 0;
--stores total error records for the summary report
l_status xx_emf_message_headers.status%TYPE;
--stores ERROR/WARNING/SUCCESS
l_error_flag VARCHAR2 (1) := 'N';
l_error_cat_flag VARCHAR2 (1) := 'N';
--Flag to capture error (Y=ERROR/N=SUCCESS)
l_warning_flag VARCHAR2 (1);
--Flag to capture warning ('W')
l_output_message VARCHAR2 (1000);
--stores the message returned from external routines
l_appl_id fnd_concurrent_requests.responsibility_application_id%TYPE;
l_resp_id fnd_concurrent_requests.responsibility_id%TYPE;
l_user_id fnd_concurrent_requests.requested_by%TYPE;
-- Common Validations Variables
/* Customer upload variables used in APIs */
p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
p_organization_rec hz_party_v2pub.organization_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
p_cust_account_id NUMBER;
p_account_number VARCHAR2 (2000);
p_party_id NUMBER;
p_party_number VARCHAR2 (2000);
p_profile_id NUMBER;
p_return_status VARCHAR2 (2000);
p_msg_count NUMBER;
p_msg_data VARCHAR2 (2000);
p_location_rec hz_location_v2pub.location_rec_type;
p_location_id NUMBER;
p_party_site_rec hz_party_site_v2pub.party_site_rec_type;
p_party_site_id NUMBER;
p_party_site_number VARCHAR2 (2000);
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
p_cust_acct_site_id NUMBER;
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cust_site_use_rec1 hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_customer_profile_rec1 hz_customer_profile_v2pub.customer_profile_rec_type;
p_customer_profile_rec2 hz_customer_profile_v2pub.customer_profile_rec_type;
p_site_use_id NUMBER;
p_relationship_rec_type hz_relationship_v2pub.relationship_rec_type;
p_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
p_cust_acct_relate_rec hz_cust_account_v2pub.cust_acct_relate_rec_type;
p_org_contact_id NUMBER;
p_party_rel_id NUMBER;
l_cust_account_id NUMBER (15);
l_party_id NUMBER (15);
l_cust_acct_site_id NUMBER (15);
l_site_use_id NUMBER (15);
l_org_contact_party_id NUMBER (15);
l_cust_acct_role_id NUMBER (15);
l_org_id NUMBER;
l_related_org_party_id NUMBER;
l_custom_profile_id NUMBER;
p_cust_account_profile_id NUMBER;
p_contact_point_id NUMBER;
p_cust_account_role_id NUMBER;
p_responsibility_id NUMBER;
p_create_profile_amt VARCHAR2 (2000);
p_contact_point_rec apps.hz_contact_point_v2pub.contact_point_rec_type;
p_person_rec apps.hz_party_v2pub.person_rec_type;
p_email_rec apps.hz_contact_point_v2pub.email_rec_type;
p_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
p_phone_rec apps.hz_contact_point_v2pub.phone_rec_type;
p_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
p_web_rec_type hz_contact_point_v2pub.web_rec_type;
p_cr_cust_acc_role_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
p_role_responsibility_rec hz_cust_account_role_v2pub.role_responsibility_rec_type;
l_counter NUMBER;
l_update_success VARCHAR2 (2);
l_update_message VARCHAR2 (255);
l_proc_stg_recs NUMBER := 0;
l_err_stg_recs NUMBER := 0;
l_succ_stg_recs NUMBER := 0;
-- Exception Variables
e_emf_initialize_error EXCEPTION;
--Stop the program if EMF initialization fails
e_emf_initialize_detail_error EXCEPTION;
--Stop the program with EMF error header insertion fails
e_raise_reject_record EXCEPTION;
e_record_count_error EXCEPTION;
e_data_insert_error EXCEPTION;
e_data_update_error EXCEPTION;
e_apps_init_error EXCEPTION;

--------------------------------------------------------------
--Main Procedure Section
-------------------------------------------------------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_run_mode IN VARCHAR2
)
IS
BEGIN --Main Begin
BEGIN
l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
l_user_id := fnd_profile.VALUE ('USER_ID');
/*----------------------------------------------------------------------------------------------------
Initialize Trace
Purpose : Set the program environment for Tracing
-----------------------------------------------------------------------------------------------------*/
xx_trace.set_program_environment (p_program_name => l_program_name);
/*----------------------------------------------------------------------------------------------------
Initialize EMF Section
Purpose : EMF is initialized so that error can be logged.
-----------------------------------------------------------------------------------------------------*/
xx_trace.vl ('Entering Program ' || l_program_name);
--Call to EMF insert_program_start
l_header_id :=
xx_emf.insert_program_start (p_program_name => l_program_name,
p_program_type => g_program_type,
p_ricew_id => l_ricewid,
p_request_id => l_request_id
);

IF (l_header_id <= 1)
THEN --Header id should be greater than 1
RAISE e_emf_initialize_error;
--Raise Exception to Stop the program if EMF initialization fails
END IF;

--
xx_trace.l ('EMF initialized, header_id :' || TO_CHAR (l_header_id));
--
/*----------------------------------------------------------------------------------------------------
Insert EMF Header
Purpose : To tag the Error labels to EMF columns
-----------------------------------------------------------------------------------------------------*/
l_error_rec.header_id := l_header_id;
--
-- Identifier to locate an error.
--
l_error_rec.identifier1 := 'Err Code'; --First Error Header
l_error_rec.identifier2 := 'Error Message'; --Second Error Header
l_error_rec.identifier3 := 'Cust Id'; --Third Error Header
l_error_rec.identifier4 := 'Error Count'; --Fourth Error Header
l_error_rec.identifier5 := 'Name'; --Fifth Error Header
l_error_rec.identifier6 := 'Type'; --Sixth Error Header
l_error_rec.identifier7 := 'API Name'; --Seventh Error Header
-- l_error_rec.identifier8 := NULL; --Eighth Error Header
--
-- Insert error header
--
l_return_value :=
xx_emf.insert_error_headers (p_error_rec => l_error_rec);

--
IF l_return_value = 1
THEN --(1 indicates Error and 0 indicates Success)
--Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
RAISE e_emf_initialize_detail_error;
END IF;

--
xx_trace.l ('EMF Error Header inserted');
l_return_value := NULL;
--
/*---------------------------------------------------------------------------------------------------
Call To Purge Program xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.purge_ricewid_dated_messages (l_ricewid,
(SYSDATE - g_retention_period
)
);

--
IF l_return_value = 1
THEN
--
l_warning_flag := 'W';
xx_trace.l ('Failed to purge old EMF records');
--
END IF;

--
--Initialize apps
xx_common_validations_pkg.init_apps_params
(p_request_id => l_request_id,
p_process_status => l_process_status,
p_error_message => l_output_message
);

IF l_process_status <> 0
THEN
--Call EMF and exit from the program
xx_trace.h ('Apps initialize problem');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00000',
p_identifier2 => 'Apps initialize problem.',
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => 'xx_common_validations_pkg.init_apps_params',
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_apps_init_error;
END IF;

IF p_run_mode = 'F'
THEN
BEGIN --validate_at_control_level starts
-- File validation starts
BEGIN
BEGIN
SELECT NVL (record_count, 0)
INTO l_file_record_count
FROM xx_ar_file_stg
WHERE status = 'NW';
EXCEPTION
WHEN OTHERS
THEN
l_file_record_count := 0;
END;

l_cust_cnt := 0;

SELECT COUNT (*)
INTO l_cust_cnt
FROM xx_ar_cust_stg
WHERE status = 'NW';

SELECT COUNT (*)
INTO l_addr_cnt
FROM xx_ar_address_stg
WHERE status = 'NW';

SELECT COUNT (*)
INTO l_contact_cnt
FROM xx_ar_contact_stg
WHERE status = 'NW';

IF l_file_record_count <>
(l_cust_cnt + l_contact_cnt + l_addr_cnt
)
THEN
UPDATE xx_ar_file_stg
SET status = 'ER';

--- Raise record count error and exit from the program
xx_trace.l ('Record count mismatch in the data file');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00001',
p_identifier2 => 'Record count mismatch.',
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => 'Record Count check at control level',
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_record_count_error;
END IF;

COMMIT;
END;

-- File validation ends
-- Customer Type validation starts
FOR r_cust_control IN c_cust_control
LOOP
IF NVL (r_cust_control.customer_type, 'X') NOT IN
('ORG', 'PERSON')
THEN
--- Update staging table record with status=ER write by EMF
--Call EMF
xx_trace.l ('Invalid Customer Type');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20
-- low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00003',
p_identifier2 => 'Invalid Customer Type:'
|| NVL
(r_cust_control.customer_type,
'NULL'
),
p_identifier3 => r_cust_control.customer_id,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
update_custom_table
(
p_customer_id => r_cust_control.customer_id,
p_req_id => NULL,
p_status => 'ER',
p_table_type => 'STAGING',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
-- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000042',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;
END IF;
END LOOP;

COMMIT;

END; --validate_at_control_level ends

BEGIN --move_data_preint_table starts
FOR r_cust_preint IN c_cust_preint
LOOP
INSERT INTO xx_ar_cust_preint
(status, request_id,
customer_id, customer_account_number,
customer_type,
customer_first_name,
customer_last_name,
customer_title,
dob, gender,
nationality,
job_title,
related_org_id,
relationship_type,
role_type,
site_use_code,
responsibility_code,
LANGUAGE,
acctg_type,
customer_class,
profile_class,
start_date_active,
end_date_active, party_id, party_number,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14,
attribute15, attribute16, attribute17,
attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23,
attribute24, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login
)
VALUES ('IP', l_request_id,
r_cust_preint.customer_id, NULL,
r_cust_preint.customer_type,
r_cust_preint.customer_first_name,
r_cust_preint.customer_last_name,
r_cust_preint.customer_title,
r_cust_preint.dob, r_cust_preint.gender,
r_cust_preint.nationality,
r_cust_preint.job_title,
r_cust_preint.related_org_id,
r_cust_preint.relationship_type,
r_cust_preint.role_type,
r_cust_preint.site_use_code,
r_cust_preint.responsibility_code,
r_cust_preint.LANGUAGE,
r_cust_preint.acctg_type,
r_cust_preint.customer_class,
r_cust_preint.profile_class,
r_cust_preint.start_date_active,
r_cust_preint.end_date_active, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, SYSDATE,
l_user_id, SYSDATE, l_user_id,
fnd_global.login_id
);

l_rec_insert_cnt := l_rec_insert_cnt + 1;

IF l_rec_insert_cnt = 1000
THEN
COMMIT;
l_rec_insert_cnt := 0;
END IF;
END LOOP;

FOR r_address_preint IN c_address_preint
LOOP
INSERT INTO xx_ar_address_preint
(status, request_id,
address_id,
primary_address,
customer_id,
address_type,
address1,
address2,
address3,
address4,
city,
state,
postal_code,
county,
country, attribute_category, attribute1,
attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13,
attribute14, attribute15, attribute16,
attribute17, attribute18, attribute19,
attribute20, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login
)
VALUES ('IP', l_request_id,
r_address_preint.address_id,
r_address_preint.primary_address,
r_address_preint.customer_id,
r_address_preint.address_type,
r_address_preint.address1,
r_address_preint.address2,
r_address_preint.address3,
r_address_preint.address4,
r_address_preint.city,
r_address_preint.state,
r_address_preint.postal_code,
r_address_preint.county,
r_address_preint.country, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, SYSDATE,
l_user_id, SYSDATE, l_user_id,
fnd_global.login_id
);

l_rec_insert_cnt := l_rec_insert_cnt + 1;

IF l_rec_insert_cnt = 1000
THEN
COMMIT;
l_rec_insert_cnt := 0;
END IF;
END LOOP;

FOR r_contact_preint IN c_contact_preint
LOOP
INSERT INTO xx_ar_contact_preint
(status, request_id,
contact_id,
customer_id,
address_id,
phone,
phone_ext,
fax,
mobile,
email,
usage_type, attribute_category, attribute1,
attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13,
attribute14, attribute15, attribute16,
attribute17, attribute18, attribute19,
attribute20, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login
)
VALUES ('IP', l_request_id,
r_contact_preint.contact_id,
r_contact_preint.customer_id,
r_contact_preint.address_id,
r_contact_preint.phone,
r_contact_preint.phone_ext,
r_contact_preint.fax,
r_contact_preint.mobile,
r_contact_preint.email,
r_contact_preint.usage_type, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, SYSDATE,
l_user_id, SYSDATE, l_user_id,
fnd_global.login_id
);

l_rec_insert_cnt := l_rec_insert_cnt + 1;

IF l_rec_insert_cnt = 1000
THEN
COMMIT;
l_rec_insert_cnt := 0;
END IF;
END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
--raise data insert error..exit from the program
xx_trace.h ('Data Insert Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00004',
p_identifier2 => 'Exception While Inserting Into Pre-Interface table.Oracle Error-'
|| SQLERRM,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);

RAISE e_data_insert_error;
END; --move_data_preint_table ends

SELECT COUNT (*)
INTO l_proc_stg_recs
FROM xx_ar_cust_stg;

SELECT COUNT (*)
INTO l_succ_stg_recs
FROM xx_ar_cust_stg
WHERE status = 'NW';

l_err_stg_recs := l_proc_stg_recs - l_succ_stg_recs;
/*----------------------------------------------------------------------------
--Inserting Summary Counts for Staging Table Records
----------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts
(p_header_id => l_header_id,
p_display_name => l_program_name
|| ': Staging',
p_total_recs => l_proc_stg_recs,
p_successful_recs => l_succ_stg_recs,
p_error_recs => l_err_stg_recs
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;
END IF; --p_run_mode='F' ends

l_cust_count := 0;

BEGIN --validate_business_level_org starts
FOR r_org_process IN c_org_process
LOOP
l_error_flag := 0;
l_cust_count := l_cust_count + 1;

IF r_org_process.customer_first_name IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Organization Name is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00005',
p_identifier2 => 'Organization Name is NULL',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_org_process.end_date_active < SYSDATE
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Inactive Customer');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00007',
p_identifier2 => 'Inactive Customer',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_org_process.profile_class IS NOT NULL
THEN
SELECT COUNT (*)
INTO l_cnt_profile
FROM ar_customer_profile_classes
WHERE NAME = r_org_process.profile_class;

IF l_cnt_profile = 0
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Invalid Profile Class');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00008',
p_identifier2 => 'Invalid Profile Class',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;

FOR r_address IN c_address (r_org_process.customer_id)
LOOP
IF r_address.address1 IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address1 is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00010',
p_identifier2 => 'Address1 is NULL',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_address.city IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address City is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00011',
p_identifier2 => 'Address City is NULL',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_address.country IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address Country is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00012',
p_identifier2 => 'Address Country is NULL',
p_identifier3 => r_org_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END LOOP;

IF l_error_flag = 1
THEN
update_custom_table
(
p_customer_id => r_org_process.customer_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000044',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_org_error := l_org_error + 1;
END IF;

IF l_org_error = 1000
THEN
COMMIT;
l_org_error := 0;
END IF;
END LOOP;

COMMIT;
END; --validate_business_level_org ends

l_success_pr := 0;

BEGIN --process_customer_org starts
l_counter := 0;

FOR r_org_process IN c_org_process
LOOP
l_counter := l_counter + 1;
process_customer_record (p_customer_process => r_org_process,
p_success => l_success,
p_error_api => l_error_api,
p_message_count => l_msg_count,
p_message_data => l_msg_data
);

IF l_success = 'N'
THEN
--Call EMF
xx_trace.m ('Oracle API Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 30 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00014',
p_identifier2 => l_msg_data,
p_identifier3 => r_org_process.customer_id,
p_identifier4 => l_msg_count,
p_identifier5 => r_org_process.customer_first_name,
p_identifier6 => 'ORG',
p_identifier7 => l_error_api,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
update_custom_table
(
p_customer_id => r_org_process.customer_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000045',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;
-- Update interface tables
ELSE
-- Update interface tables
update_custom_table
(
p_customer_id => r_org_process.customer_id,
p_req_id => l_request_id,
p_status => 'PR',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000046',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_success_pr := l_success_pr + 1;
END IF;

IF l_counter = 1000
THEN
COMMIT;
l_counter := 0;
END IF;
END LOOP;

COMMIT;
END; --process_customer_org ends

BEGIN --validate_business_level_person starts
FOR r_person_process IN c_person_process
LOOP
l_error_flag := 0;
l_cust_count := l_cust_count + 1;

IF r_person_process.customer_first_name IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('First Name Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00016',
p_identifier2 => 'First Name Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_person_process.customer_last_name IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Last Name Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00017',
p_identifier2 => 'Last Name Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_person_process.customer_title IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Title Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00018',
p_identifier2 => 'Title Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_person_process.end_date_active < SYSDATE
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Customer Is Inactive');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00020',
p_identifier2 => 'Customer Is Inactive',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_person_process.profile_class IS NOT NULL
THEN
SELECT COUNT (*)
INTO l_cnt_profile
FROM ar_customer_profile_classes
WHERE NAME = r_person_process.profile_class;

IF l_cnt_profile = 0
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Invalid Profile Class');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00021',
p_identifier2 => 'Invalid Profile Class',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;

IF r_person_process.related_org_id IS NOT NULL
THEN
SELECT COUNT (*)
INTO l_cnt_org
FROM hz_cust_accounts_all a
WHERE orig_system_reference =
r_person_process.related_org_id;

IF l_cnt_org = 0
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Related Organization does not exist');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00022',
p_identifier2 => 'Related Organization does not exist',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END IF;

FOR r_address IN c_address (r_person_process.customer_id)
LOOP
IF r_address.address1 IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address1 Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00024',
p_identifier2 => 'Address1 Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_address.city IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address City Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00025',
p_identifier2 => 'Address City Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;

IF r_address.country IS NULL
THEN
l_error_flag := 1;
--Call EMF
xx_trace.l ('Address Country Is NULL');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 20 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00026',
p_identifier2 => 'Address Country Is NULL',
p_identifier3 => r_person_process.customer_id,
p_identifier4 => '1',
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
END IF;
END LOOP;

IF l_error_flag = 1
THEN
update_custom_table
(
p_customer_id => r_person_process.customer_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000047',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_person_error := l_person_error + 1;
END IF;

IF l_person_error = 1000
THEN
COMMIT;
l_person_error := 0;
END IF;
END LOOP;

COMMIT;
END; --validate_business_level_person ends

BEGIN --process_customer_person starts
FOR r_person_process IN c_person_process
LOOP
l_counter := 0;
process_customer_record
(p_customer_process => r_person_process,
p_success => l_success,
p_error_api => l_error_api,
p_message_count => l_msg_count,
p_message_data => l_msg_data
);

IF l_success = 'N'
THEN
--Call EMF
xx_trace.m ('Oracle API Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 30 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-00015',
p_identifier2 => l_msg_data,
p_identifier3 => r_person_process.customer_id,
p_identifier4 => l_msg_count,
p_identifier5 => r_person_process.customer_first_name,
p_identifier6 => 'PERSON',
p_identifier7 => l_error_api,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
update_custom_table
(
p_customer_id => r_person_process.customer_id,
p_req_id => l_request_id,
p_status => 'ER',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000049',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;
-- Update interface tables
ELSE
-- Update interface tables
update_custom_table
(
p_customer_id => r_person_process.customer_id,
p_req_id => l_request_id,
p_status => 'PR',
p_table_type => 'PREINT',
p_success => l_update_success,
p_message_data => l_update_message
);

IF l_update_success = 'F'
THEN

xx_trace.h ('Data Update Error');
--Write to Error
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40 -- high level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000048',
p_identifier2 => 'Exception While Update.Oracle Error-'
|| l_update_message,
p_identifier3 => NULL,
p_identifier4 => '1',
p_identifier5 => NULL,
p_identifier6 => NULL,
p_identifier7 => NULL,
p_identifier8 => NULL,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE e_data_update_error;
END IF;

l_success_pr := l_success_pr + 1;
END IF;

IF l_counter = 1000
THEN
COMMIT;
l_counter := 0;
END IF;
END LOOP;

COMMIT;
END; --process_customer_person ends

/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/
BEGIN
--
DELETE FROM xx_ar_cust_preint
WHERE status = 'PR';

--
DELETE FROM xx_ar_contact_preint
WHERE status = 'PR';

--
DELETE FROM xx_ar_address_preint
WHERE status = 'PR';

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
xx_trace.h
( 'Exception while deleting records from pre-interface table'
|| SQLERRM
);
END;

/*----------------------------------------------------------------------------
Call Insert Summary Count
Purpose: Display a summary of records processed, successful and in error
----------------------------------------------------------------------------*/
l_error_recs := l_cust_count - l_success_pr;
l_return_value := NULL;
--
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
p_total_recs => l_cust_count,
p_successful_recs => l_success_pr,
p_error_recs => l_error_recs
);

--
IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to Insert Summary Count');
END IF;

--
/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);

IF l_return_value = 1
THEN
l_warning_flag := 'W';
xx_trace.l ('Failed to print EMF messages on the output');
END IF;

--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*----------------------------------------------------------------------------
Set display width for output
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
--
/*----------------------------------------------------------------
Call to xx_emf.display_output_messages
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);

IF l_return_value = 1
THEN
l_error_flag := 'Y';
xx_trace.l ('Failed to display output messages');
END IF;

--
l_return_value := NULL;

--
/*---------------------------------------------------
Call ToEMF Update_program_status in EMF tables
-----------------------------------------------------*/
IF l_error_flag = 'Y'
THEN --ERROR
l_warning_flag := ''; --error status supercedes warning
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_warning_flag = 'W'
THEN
l_status := 'WARNING';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status');
l_return_value := NULL;
END IF;
ELSIF l_error_flag = 'N'
THEN
l_status := 'SUCCESS';
l_return_value := NULL;
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

--
IF l_return_value = 1
THEN
xx_trace.l ('Failed to update program status for Success');
END IF;

COMMIT;
END IF;
EXCEPTION
--Call emf_initialize_error if EMF header fails
WHEN e_emf_initialize_error
THEN
xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
WHEN e_emf_initialize_detail_error
THEN
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
xx_trace.l ('Failed to insert EMF error header');
WHEN e_record_count_error
THEN
l_error_recs := l_cust_cnt;
RAISE;
WHEN e_apps_init_error
THEN
RAISE;
WHEN e_data_update_error
THEN
RAISE;
WHEN e_data_insert_error
THEN
RAISE;
WHEN OTHERS
THEN
xx_emf.call_store_message
(p_message_group => 'ERR_DTL',
p_header_id => l_header_id,
p_debug_value => 40
--High level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'E-000041',
p_identifier2 => 'Exception in Main.'
|| SQLERRM,
p_process_status => l_return_value,
p_error_message => l_output_message
);
RAISE;
END;
EXCEPTION --Main Exception
--Main Exception Block
WHEN OTHERS
THEN
COMMIT;

--Write to error
l_error_flag := 'Y';
--
l_messages_rec := l_null_rec;
l_return_value := NULL;
/*-------------------------------------------------------------------------------------
Call To Insert Summary Count
------------------------------------------------------------------------------------*/
l_return_value :=
xx_emf.insert_summary_counts (p_header_id => l_header_id,
p_display_name => l_program_name,
p_total_recs => l_cust_cnt,
p_successful_recs => l_success_pr,
p_error_recs => l_error_recs
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to insert summary counts');
END IF;

/*-----------------------------------------------------------------------------------------
Call flush PL/SQL table section
Purpose : Flush data from pl/sql table isrespective of the count.
Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/
xx_emf.call_store_message (p_message_group => 'ALL',
p_header_id => l_header_id,
p_debug_value => 20
--Low level debugging
,
p_global_debug => g_debug_level,
p_identifier1 => 'Flushing Data into EMF',
p_process_status => l_return_value,
p_error_message => l_output_message
);

IF l_return_value = 1
THEN
l_return_value := NULL;
xx_trace.h ('Unable to call EMF error log');
l_return_value :=
xx_emf.update_program_status (l_header_id, l_status);
END IF;

l_return_value := NULL;
/*----------------------------------------------------------------------------
Purpose: Call to set EMF error and report section column widths
--------------------------------------------------------------------------*/
l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
/*----------------------------------------------------------------
Purpose--Call to generate output file (Errors/Messages)
--------------------------------------------------------------*/
l_err_width_rec.header_id := l_header_id;
l_return_value :=
xx_emf.display_output_messages (p_header_id => l_header_id,
p_err_width_rec => l_err_width_rec
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to display output messages');
END IF;

l_return_value := NULL;
/*-------------------------------------------------------------------
Call update program status
---------------------------------------------------------------------*/
l_status := 'ERROR';
l_return_value :=
xx_emf.update_program_status (p_header_id => l_header_id,
p_status => l_status
);

IF l_return_value = 1
THEN
xx_trace.h ('Unable to update program status');
l_return_value := NULL;
END IF;
END main;

/*-----------------------------------------------------------

Purpose-Procedure to Process Customer record
--
-------------------------------------------------------------*/
PROCEDURE process_customer_record (
p_customer_process IN xx_ar_cust_preint%ROWTYPE,
p_success OUT VARCHAR2,
p_error_api OUT VARCHAR2,
p_message_count OUT NUMBER,
p_message_data OUT VARCHAR2
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
p_success := 'S';
l_org_id := fnd_profile.VALUE ('ORG_ID');

-- Create Customer Account
IF p_customer_process.profile_class IS NOT NULL
THEN
SELECT customer_profile_class_id
INTO l_custom_profile_id
FROM ar_customer_profile_classes
WHERE NAME = p_customer_process.profile_class;
END IF;

IF p_customer_process.customer_type = 'PERSON'
AND p_customer_process.related_org_id IS NOT NULL
THEN
SELECT party_id
INTO l_related_org_party_id
FROM hz_cust_accounts
WHERE orig_system_reference =
TO_CHAR (p_customer_process.related_org_id);
END IF;

IF p_customer_process.customer_type = 'PERSON'
THEN
p_person_rec.person_first_name :=
p_customer_process.customer_first_name;
p_person_rec.person_last_name :=
p_customer_process.customer_last_name;
p_person_rec.person_pre_name_adjunct :=
p_customer_process.customer_title;
p_person_rec.gender := p_customer_process.gender;
p_person_rec.date_of_birth := p_customer_process.dob;
p_person_rec.person_name_phonetic := p_customer_process.job_title;
p_person_rec.party_rec.orig_system_reference :=
p_customer_process.customer_id;
p_person_rec.party_rec.attribute1 := p_customer_process.LANGUAGE;
p_person_rec.party_rec.attribute2 := p_customer_process.nationality;
p_cust_account_rec.account_number :=
p_customer_process.customer_account_number;
p_cust_account_rec.orig_system_reference :=
p_customer_process.customer_id;
p_cust_account_rec.customer_class_code :=
p_customer_process.customer_class;
p_cust_account_rec.created_by_module := 'AR01 Conv';
p_cust_account_rec.customer_type := 'R';
--Hard Coded(External customer)
p_customer_profile_rec.profile_class_id := l_custom_profile_id;
p_customer_profile_rec.site_use_id := NULL;
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_person_rec,
p_customer_profile_rec,
'F',
p_cust_account_id,
p_account_number,
p_party_id,
p_party_number,
p_profile_id,
p_return_status,
p_msg_count,
p_msg_data
);
ELSE
p_cust_account_rec.account_name :=
p_customer_process.customer_first_name;
p_cust_account_rec.account_number :=
p_customer_process.customer_account_number;
p_cust_account_rec.orig_system_reference :=
p_customer_process.customer_id;
p_cust_account_rec.customer_class_code :=
p_customer_process.customer_class;
p_cust_account_rec.created_by_module := 'AR01 Conv';
p_cust_account_rec.customer_type := 'R';
--Hard Coded(External customer)
p_organization_rec.organization_name :=
p_customer_process.customer_first_name;
p_organization_rec.created_by_module := 'AR01 Conv';
p_customer_profile_rec.profile_class_id := l_custom_profile_id;
p_customer_profile_rec.site_use_id := NULL;
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_organization_rec,
p_customer_profile_rec,
'F',
p_cust_account_id,
p_account_number,
p_party_id,
p_party_number,
p_profile_id,
p_return_status,
p_msg_count,
p_msg_data
);
END IF;

l_party_id := p_party_id;
l_cust_account_id := p_cust_account_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api := 'hz_cust_account_v2pub.create_cust_account';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'hz_cust_account_v2pub.create_cust_account';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

IF p_customer_process.customer_type =
'PERSON'
--Only for PERSON TYPE starts
THEN
--Create Relation Contact and Employee
IF p_customer_process.job_title IS NOT NULL
AND p_customer_process.related_org_id IS NOT NULL
THEN
p_org_contact_rec.job_title_code := p_customer_process.job_title;
p_org_contact_rec.orig_system_reference :=
p_customer_process.customer_id;
p_org_contact_rec.party_rel_rec.object_id :=
l_related_org_party_id;
/* Reffering Party ID created by Create Customer API */
p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.subject_id := l_party_id;
/* Reffering Party ID created by Create Person API */
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name :=
'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code :=
p_customer_process.responsibility_code;
p_org_contact_rec.party_rel_rec.relationship_type :=
p_customer_process.relationship_type;
p_org_contact_rec.created_by_module := 'AR01 Conv';
hz_party_contact_v2pub.create_org_contact ('T',
p_org_contact_rec,
p_org_contact_id,
p_party_rel_id,
p_party_id,
p_party_number,
p_return_status,
p_msg_count,
p_msg_data
);
l_org_contact_party_id := p_party_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_party_contact_v2pub.create_org_contact';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'hz_party_contact_v2pub.create_org_contact';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

--Create Customer Role
p_cr_cust_acc_role_rec.party_id := l_org_contact_party_id;
/* l_party_id Reffering value for party_id from create_org_contact */
p_cr_cust_acc_role_rec.cust_account_id := l_cust_account_id;
p_cr_cust_acc_role_rec.orig_system_reference :=
p_customer_process.customer_id;
p_cr_cust_acc_role_rec.role_type := p_customer_process.role_type;
p_cr_cust_acc_role_rec.created_by_module := 'AR01 Conv';
hz_cust_account_role_v2pub.create_cust_account_role
('T',
p_cr_cust_acc_role_rec,
p_cust_account_role_id,
p_return_status,
p_msg_count,
p_msg_data
);
l_cust_acct_role_id := p_cust_account_role_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_cust_account_role_v2pub.create_cust_account_role';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_cust_account_role_v2pub.create_cust_account_role';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

--Create Customer Responsibility
p_role_responsibility_rec.cust_account_role_id :=
l_cust_acct_role_id;
/* Reffering value for p_cust_account_role_id from above */
p_role_responsibility_rec.responsibility_type :=
p_customer_process.site_use_code;
p_role_responsibility_rec.orig_system_reference :=
p_customer_process.customer_id;
p_role_responsibility_rec.created_by_module := 'AR01 Conv';
hz_cust_account_role_v2pub.create_role_responsibility
('T',
p_role_responsibility_rec,
p_responsibility_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_cust_account_role_v2pub.create_role_responsibility';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_cust_account_role_v2pub.create_role_responsibility';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;
END IF; --Only for PERSON TYPE ends

--Create Location
FOR address IN c_address (p_customer_process.customer_id)
LOOP
fnd_client_info.set_org_context (TO_CHAR (l_org_id));
p_location_rec.country := NVL (address.country, '');
p_location_rec.address1 := address.address1;
p_location_rec.address2 := NVL (address.address2, '');
p_location_rec.address3 := NVL (address.address3, '');
p_location_rec.address4 := NVL (address.address4, '');
p_location_rec.orig_system_reference := address.address_id;
p_location_rec.city := NVL (address.city, '');
p_location_rec.postal_code := NVL (address.postal_code, '');
p_location_rec.state := NVL (address.state, '');
p_location_rec.county := NVL (address.county, '');
p_location_rec.created_by_module := 'AR01 Conv';
hz_location_v2pub.create_location ('T',
p_location_rec,
p_location_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api := 'hz_location_v2pub.create_location';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'hz_location_v2pub.create_location';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

--Create Party Site
p_party_site_rec.party_id := l_party_id;
p_party_site_rec.location_id := p_location_id;
p_party_site_rec.identifying_address_flag :=
NVL (address.primary_address, 'N');
p_party_site_rec.orig_system_reference :=
NVL (address.address_id, '');
p_party_site_rec.created_by_module := 'AR01 Conv';
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
p_party_site_id,
p_party_site_number,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api := 'hz_party_site_v2pub.create_party_site';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api := 'hz_party_site_v2pub.create_party_site';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

-- Cust Acct Site
p_cust_acct_site_rec.cust_account_id := l_cust_account_id;
p_cust_acct_site_rec.party_site_id := p_party_site_id;
p_cust_acct_site_rec.LANGUAGE := 'US';
p_cust_acct_site_rec.orig_system_reference :=
NVL (address.address_id, '');
p_cust_acct_site_rec.created_by_module := 'AR01 Conv';
hz_cust_account_site_v2pub.create_cust_acct_site
('T',
p_cust_acct_site_rec,
p_cust_acct_site_id,
p_return_status,
p_msg_count,
p_msg_data
);
l_cust_acct_site_id := p_cust_acct_site_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_cust_account_site_v2pub.create_cust_acct_site';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_cust_account_site_v2pub.create_cust_acct_site';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

-- Create Cust Site Use
IF address.address_type IS NOT NULL
THEN
p_cust_site_use_rec.cust_acct_site_id := l_cust_acct_site_id;
p_cust_site_use_rec.site_use_code := address.address_type;
p_cust_site_use_rec.orig_system_reference :=
NVL (address.address_id, '');
p_cust_site_use_rec.created_by_module := 'AR01 Conv';
p_cust_site_use_rec.gl_id_rec := '';

IF NVL (address.primary_address, 'N') = 'Y'
THEN
p_cust_site_use_rec.primary_flag := 'Y';
ELSE
p_cust_site_use_rec.primary_flag := 'N';
END IF;

hz_cust_account_site_v2pub.create_cust_site_use
('T',
p_cust_site_use_rec,
p_customer_profile_rec1,
'',
'',
p_site_use_id,
p_return_status,
p_msg_count,
p_msg_data
);
l_site_use_id := p_site_use_id;

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_cust_account_site_v2pub.create_cust_site_use';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_cust_account_site_v2pub.create_cust_site_use';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;

--Create Cust Profile
p_customer_profile_rec.created_by_module := 'AR01 Conv';
p_customer_profile_rec.site_use_id := l_site_use_id;
p_customer_profile_rec.cust_account_id := l_cust_account_id;
p_customer_profile_rec.profile_class_id := l_custom_profile_id;
hz_customer_profile_v2pub.create_customer_profile
('T',
p_customer_profile_rec,
p_create_profile_amt,
p_cust_account_profile_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_customer_profile_v2pub.create_customer_profile';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_customer_profile_v2pub.create_customer_profile';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;

IF p_customer_process.customer_type = 'ORG'
THEN
p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
p_contact_point_rec.owner_table_id := p_party_site_id;
ELSE
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := l_party_id;
END IF;

FOR contacts IN c_contacts (p_customer_process.customer_id,
address.address_id
)
LOOP
--Create Email
IF contacts.email IS NOT NULL
THEN
p_contact_point_rec.contact_point_type := 'EMAIL';
p_contact_point_rec.status := 'A';
p_contact_point_rec.primary_flag := 'N';
p_contact_point_rec.contact_point_purpose :=
contacts.usage_type;
p_contact_point_rec.created_by_module := 'AR01 Conv';
p_email_rec.email_format := 'MAILHTML';
p_email_rec.email_address := contacts.email;
hz_contact_point_v2pub.create_email_contact_point
('T',
p_contact_point_rec,
p_email_rec,
p_contact_point_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_contact_point_v2pub.create_email_contact_point';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_contact_point_v2pub.create_email_contact_point';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;

-- Create Phone
IF contacts.phone IS NOT NULL
THEN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.status := 'A';
p_contact_point_rec.primary_flag := 'N';
p_contact_point_rec.contact_point_purpose :=
contacts.usage_type;
p_contact_point_rec.created_by_module := 'AR01 Conv';
p_phone_rec.phone_area_code := '';
p_phone_rec.phone_country_code := '';
p_phone_rec.phone_number := contacts.phone;
p_phone_rec.phone_extension := contacts.phone_ext;
p_phone_rec.phone_line_type := 'TELEPHONE';
--'OFFICE'; --Hard Coded
apps.hz_contact_point_v2pub.create_phone_contact_point
('T',
p_contact_point_rec,
p_phone_rec,
p_contact_point_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;

--Create Mobile
IF contacts.mobile IS NOT NULL
THEN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.status := 'A';
p_contact_point_rec.primary_flag := 'N';
p_contact_point_rec.contact_point_purpose :=
contacts.usage_type;
p_contact_point_rec.created_by_module := 'AR01 Conv';
p_phone_rec.phone_area_code := '';
p_phone_rec.phone_country_code := '';
p_phone_rec.phone_number := contacts.mobile;
p_phone_rec.phone_extension := NULL;
p_phone_rec.phone_line_type := 'MOBILE';
apps.hz_contact_point_v2pub.create_phone_contact_point
('T',
p_contact_point_rec,
p_phone_rec,
p_contact_point_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;

-- Fax
IF contacts.fax IS NOT NULL
THEN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.status := 'A';
p_contact_point_rec.primary_flag := 'N';
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.created_by_module := 'AR01 Conv';
p_phone_rec.phone_area_code := '';
p_phone_rec.phone_country_code := '';
p_phone_rec.phone_number := contacts.fax;
p_phone_rec.phone_extension := NULL;
p_phone_rec.phone_line_type := 'FAX';
apps.hz_contact_point_v2pub.create_phone_contact_point
('T',
p_contact_point_rec,
p_phone_rec,
p_contact_point_id,
p_return_status,
p_msg_count,
p_msg_data
);

IF p_return_status <> fnd_api.g_ret_sts_success
THEN
IF p_msg_count > 1
THEN
FOR i IN 1 .. 1
LOOP
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
p_message_count := p_msg_count;
p_message_data :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END LOOP;
ELSE
p_message_data := p_msg_data;
p_message_count := p_msg_count;
p_error_api :=
'hz_contact_point_v2pub.create_phone_contact_point';
END IF;

p_success := 'N';
RAISE e_raise_reject_record;
END IF;
END IF;
END LOOP; -- Contact cursor ends
END LOOP; --Address Cursor ends

COMMIT;
EXCEPTION
WHEN e_raise_reject_record
THEN
ROLLBACK;
WHEN OTHERS
THEN
ROLLBACK;
p_error_api := 'process_customer_record';
p_message_count := 0;
p_message_data := SUBSTR (SQLERRM, 1, 255);
p_success := 'N';
END process_customer_record;

PROCEDURE update_custom_table (
p_customer_id IN xx_ar_cust_stg.customer_id%TYPE,
p_req_id IN NUMBER,
p_status IN VARCHAR2,
p_table_type IN VARCHAR2,
p_success OUT VARCHAR2,
p_message_data OUT VARCHAR2
)
AS
BEGIN
p_success := 'S';
p_message_data := NULL;

IF p_table_type = 'STAGING'
THEN
UPDATE xx_ar_cust_stg
SET status = p_status
WHERE customer_id = p_customer_id;

UPDATE xx_ar_address_stg
SET status = p_status
WHERE customer_id = p_customer_id;

UPDATE xx_ar_contact_stg
SET status = p_status
WHERE customer_id = p_customer_id;
ELSIF p_table_type = 'PREINT'
THEN
UPDATE xx_ar_cust_preint
SET status = p_status,
request_id = p_req_id
WHERE customer_id = p_customer_id;

UPDATE xx_ar_address_preint
SET status = p_status,
request_id = p_req_id
WHERE customer_id = p_customer_id;

UPDATE xx_ar_contact_preint
SET status = p_status,
request_id = p_req_id
WHERE customer_id = p_customer_id;
ELSE
p_success := 'F';
p_message_data := 'Invalid Input Supplied';
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_success := 'F';
p_message_data := SUBSTR (SQLERRM, 1, 255);
END update_custom_table;
END xx_arcnv01_customer_load_pkg;
/

DFF personalization

2013-02-25T23:54:00.002+05:30

PO Header Dff Creation:

If you want to add one more attribute column first uncheck the “Freeze Flexfield definition” then all fields will be editable.

PO Release DFF:

Purchase Order form :

Save and close and re-open the Releases form.

Then give PO Number press tab then Buyer name will populate automatically. If you see DFF value it will be null. Just go to shipment tab and come back to header DFF this time the DFF value will populate.

Oracle Apps Fresher Interview Questions

2013-02-11T13:49:00.002+05:30

1. Which modules you trained in oracle apps?

A: Financial modules : AP,AR,GL

SCM Modules : PO,OM,INV

2.Which reports you've done?

i. Stack Summary Report

ii.Return to Vendor Reprot

iii.Goods Receipt Note (GRN)

iv.Approved Requisition Report

v.Back Order Report

vi.GL Report

vii.Sales OrderReport.

viii.Vendorwise PO Approved Report

ix.AR Receipt Information Report.

3.Which Interfaces have you done?

AP Interface

PO Interface

GL Interface

4.What is Interface?

Interface is the process of transferring data from one system to another system.

5.what is the use of Inbound Interface?

It is used to transfer the data from legacy system to oracle applications.

6.Explain about AP Interface?

Client give us data file

Then we have design 2 temporary staging tables one for invoices and another one for invoices lines.

Then we dump the data from data/f lat file to staging tables through SQL *loader.

Then we will create one Package with 2 procedures, In that we create 2 cursors one for invoices and another one for invoices lines.

Take 2 variables and validate mandatory columns and send data into variables.

Then insert the data into Interface tables.

Run Standard Concurrent Program "Payables Open Interface Import " through SRS window.

Then finally we will see data in base tables.

7.What are mandatory columns in AP Interface?

Invoice Header level.

Operating Unit, Vendor, Invoice Number, Vendor Site, Invoice type, Invoice amount, Invoice Date, Invoice Source

Invoice Line Level.

Invoice line Type, Account, Line Number, Line Amount.

8.Which tables did you used in AP Interface?

Interface Tables :

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

AP_INTERFACE_REJECTIONS

Base Tables :

AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

9.Difference between Conversion and Interface?

Interface Conversion

To transfer daily transactions from Legacy system to Oracle Apps	To transfer old data from Legacy system to oracle Apps or Lower Version to Higher version (11i -->R12)
It is on demand process/scheduled process	It is one time process
It is applicable in After Implementation	It is applicable in Conversion Projects and Up gradation Projects.
We don't know data volume	We know the data volume

10.When will we use conversion?

11.How to display vendor details in GL Report and what is the link?

We will display vendor details in GL Report by taking AP tables.

link is PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID

Then

AP_AE_LINES.GL_SL_LINK_ID=GL_JE_LINES.GL_SL_LINK_ID

12.What is Procedure?

is a named pl/sql block to perform a specific task.

---- A procedure may have DML statements.

---- It may or may not return a value.

---- Procedure can return more than one value.

13.What is Function? When will we use it?

is a named pl/sql block to perform a specific task, is mainly used for calculation purpose.

---- A function is called as part of an exception.

---- Every function should return a value

If u want to do any calculations then we will use Function.

14.XML Registration steps?

1. Register the E-business suite report as a Data Definition in the Template Manager.

Create a Data definition in the Template Manager for E-business Suite reports that

We wish to customize using XML Publisher. When you create the Data Definition

Code must match the E-Business Suite report short name.

2. Design our template

Our template files can be either in Rich Text Format (RTF) or Portable Document.

RTF is a specification used by many word processing applications, such as

Microsoft word. We design the template using our desktop word processing

Application and save the file as an RTF file type (.rtf extension). Insert basic

Markup tags to the document to prepare it for merging with the XML data. XML

Recognizes the formatting features that we apply and converts them to XSL-FO.

Use Adobe Acrobat to apply markup tags to your custom-designed or

Downloaded PDF template

3. Register out template in the Template Manager

When we create the Template Manager, we register and upload our RTF

Or PDF template files. The template must be assigned to the Data definition

Code of the E-Business Suite report with which it will be merged.

4. Add desired translations of your RTF template

XML Publisher’s translation utility allows us to extract the translatable strings

From your template into an XLIFF file. Translate the strings in this file and re-

upload to the Template Manager to make translation available at runtime.

Run Time: -

1. Set the concurrent program to generate XML.

2. Run the concurrent program using standard request submission to obtain the XML

Output.

3. Run the XML Publisher Concurrent Request .

The XML Publisher Concurrent Request will prompt us to enter the Request ID from the previous step, and t select a template, template locale, and output type. Available templates are those associated to the report Data Definition in the Template Manager. XML Publisher merges our design template with the XML data to generate your customized output.

15.PO Process?

16.RDF Registration steps?

17.What is Form Personalization? Which pages you personalize?

18.Which triggers you use in Form Personalization?

19.What is exception? How many types?

20. Difference between Predefined exceptions and User defined exceptions?

21.If we write any exception after when others then what will be the error?

22.Can we create Package Body without specification?

23.Can we create Package Specification without Body?

24.Form Customization steps? When will we give form name in Registration?

25.What is Profile? How many levels?

26.Whar are Site level Profiles?

27.Which profile in GL?

28.Which Profile added in R12?

29.What is Sql*loader? Which command used in Sql*loader to load data?

30.What is TCA? What are the changes made in TCA in R12?

31. What are PO Tables?

31. What are Inventory Tables?

33.What are Sub ledger Accounting Tables?

If you know any answer of the above Question please type in comment box by giving Question no.

Re - Grouping input XML before applying to template

2013-02-06T13:05:00.000+05:30

Re - Grouping input XML before applying to template

In this lesson we are going to construct an RDF with out any grouping but we can regroup it in the RTF which is shown below.

Now we have to construct an RDF by using below Query

SELECT pha.segment1 po_number,

              pha.creation_date po_date,

              pha.type_lookup_code,

              pv.segment1 vendor_number,

              pv.vendor_name,

              pla.line_num,

              pla.unit_price unit_price,

              pla.quantity quantity

  FROM po_headers_all pha,

               po_vendors pv,

               po_lines_all pla,

               po_line_locations_all plla,

               mtl_system_items_b msi

 WHERE pha.vendor_id = pv.vendor_id

   AND    pha.po_header_id = pla.po_header_id

   AND    pla.po_line_id = plla.po_line_id

   AND    pla.item_id = msi.inventory_item_id

   AND    plla.ship_to_location_id = msi.organization_id

   AND    ROWNUM < 101

Our RDF looks

Now we have to generate XML .

Our XML looks

Now we have to construct RTF.

Out RTF looks

In the Add Help Text we have to write below command for each field

Now we have to publish the report

Click on Template Builder => Preview => HTML/PDF/Excel/RTF (any format).

We can get below output

Birla Temple Photos

2013-01-10T15:22:00.004+05:30

Birla Temple Tour with Malli,Amar and Hari on 20th Dec 2012

Water In my Garden

2013-01-06T16:06:00.001+05:30

Simhadripuram Theatre

2013-01-06T16:03:00.001+05:30

Nirupam Reddy

2013-01-06T15:57:00.002+05:30

My brother-in-law Nirupam Reddy at water Bore