Concurrent Program attached to Responsibilities Query

select frt.RESPONSIBILITY_NAME
,fcpt.USER_CONCURRENT_PROGRAM_NAME
from
fnd_request_groups frg,
FND_REQUEST_GROUP_UNITS frgu,
fnd_concurrent_programs_tl fcpt,
fnd_responsibility fr,
fnd_responsibility_tl frt
where frg.request_group_id=frgu.request_group_id
and frgu.request_unit_type='P'
and frgu.request_unit_id=fcpt.concurrent_program_id
and fr.REQUEST_GROUP_ID=frg.REQUEST_GROUP_ID
and fr.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID
and fcpt.USER_CONCURRENT_PROGRAM_NAME='Autoinvoice Master Program'

Customer Interface Program

/********************************************************************************/
/* */
/* Script file for Customer interface. */
/* Created by Srikanth Kotipalli. */
/* Created on 12/31/2008. */
/* */
/********************************************************************************/

/* Staging Table. */
CREATE TABLE xxtr_ksk_cust_data( cust_number VARCHAR2(240)
, cust_name VARCHAR2(360)
, address1 VARCHAR2(240)
, address2 VARCHAR2(240)
, city VARCHAR2(60)
, state VARCHAR2(60)
, country VARCHAR2(60)
, cust_ship_via_code VARCHAR2(30)
, orig_system_address_ref VARCHAR2(240)
, site_use_code VARCHAR2(30)
, profile_class VARCHAR2(30) DEFAULT 'DEFAULT'
, status VARCHAR2(25) DEFAULT 'Not Processed'
, description VARCHAR2(100)
);
/


/*Insert data into Staging Table.*/
INSERT INTO xxtr_ksk_cust_data values(2,'XYZ','GA-16, SBH COLONY','DILSUKHNAGAR','HYDERABAD','AP','IN','DHL','2','SOLD_TO','Average');
/

/*Package Specification.*/
CREATE OR REPLACE PACKAGE xxtr_ksk_cust_pkg
IS
PROCEDURE xxtr_ksk_cust_insert(errbuf OUT VARCHAR2,retcode OUT NUMBER);
END xxtr_ksk_cust_pkg;
/

/*Package Body*/
CREATE OR REPLACE PACKAGE BODY xxtr_ksk_cust_pkg
IS
/*Procedure to insert data from staging table to interface table.*/
PROCEDURE xxtr_ksk_cust_insert (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
/* Local variable declaration.*/
lv_org_id NUMBER (15);
lv_count_site_use NUMBER (3);
lv_count_profile_class NUMBER (3);
lv_req_id NUMBER (15);
lv_cust_count NUMBER (3);
lv_count NUMBER (3) := 0;
lv_description VARCHAR2(100);

/* Cursor Declaration.*/
CURSOR cur_cust_data
IS
SELECT *
FROM xxtr_ksk_cust_data
WHERE status <> 'Processed' OR status is null;
BEGIN
lv_org_id := fnd_profile.VALUE ('org_id'); --To get Org_Id.

/* Cursor for loop to fetch the data from staging table.*/
FOR cur_cust_data_rec IN cur_cust_data
LOOP
BEGIN
/* Select statement to check whether the site_use_code is valid or not.*/
SELECT COUNT (1)
INTO lv_count_site_use
FROM ar_lookups
WHERE lookup_type = 'SITE_USE_CODE'
AND lookup_code = cur_cust_data_rec.site_use_code;

/* Select statement to check whether the profile class name is valid or not.*/
SELECT COUNT (1)
INTO lv_count_profile_class
FROM hz_cust_profile_classes
WHERE NAME = cur_cust_data_rec.profile_class AND status = 'A';

SELECT COUNT (1)
INTO lv_cust_count
FROM ar_customers
WHERE customer_name = cur_cust_data_rec.cust_name
AND status = 'A';

IF (lv_cust_count = 0)
THEN
IF (lv_count_site_use > 0)
THEN
/*Insert data into interface table ra_customers_interface_all from staging table.*/
INSERT INTO ra_customers_interface_all ( orig_system_customer_ref
, insert_update_flag
, customer_name
, primary_site_use_flag
, customer_status
, address1
, address2
, city
, state
, country
, cust_ship_via_code
, last_updated_by
, last_update_date
, created_by
, creation_date
, org_id
, orig_system_address_ref
, site_use_code
)
VALUES ( cur_cust_data_rec.cust_number
, 'I'
, cur_cust_data_rec.cust_name
, 'Y'
, 'A'
, cur_cust_data_rec.address1
, cur_cust_data_rec.address2
, cur_cust_data_rec.city
, cur_cust_data_rec.state
, cur_cust_data_rec.country
, cur_cust_data_rec.cust_ship_via_code
, -1
, SYSDATE
, -1
, SYSDATE
, lv_org_id
, cur_cust_data_rec.orig_system_address_ref
, cur_cust_data_rec.site_use_code
);

IF (lv_count_profile_class > 0)
THEN
/* Insert data into interface table ra_customer_profiles_interface from staging table.*/
INSERT INTO ra_customer_profiles_int_all ( orig_system_customer_ref
, insert_update_flag
, credit_hold
, created_by
, creation_date
, last_updated_by
, last_update_date
, customer_profile_class_name
, org_id
)
VALUES ( cur_cust_data_rec.cust_number
, 'I'
, 'Y'
, -1
, SYSDATE
, -1
, SYSDATE
, cur_cust_data_rec.profile_class
, lv_org_id
);

/* Updating status of the staging table for the record.*/
UPDATE xxtr_ksk_cust_data
SET status = 'Processed'
, description = 'Valid'
WHERE cust_number = cur_cust_data_rec.cust_number;

lv_count := lv_count + 1;
ELSE
fnd_file.put_line (fnd_file.LOG,
'Profile class is not valid'
);

/* Updating status of the staging table for the record.*/
UPDATE xxtr_ksk_cust_data
SET status = 'Rejected'
, description='Profile class is not valid'
WHERE cust_number = cur_cust_data_rec.cust_number;


END IF;
ELSE
fnd_file.put_line
(fnd_file.LOG,
'site_use_code or profile class is not valid'
);

/* Updating status of the staging table for the record.*/
UPDATE xxtr_ksk_cust_data
SET status = 'Rejected'
, description='Site use code is not valid'
WHERE cust_number = cur_cust_data_rec.cust_number;


END IF;
ELSE
fnd_file.put_line (fnd_file.LOG,
'The customer already exists for customer number :'||cur_cust_data_rec.cust_number);

lv_description:=lv_description||'Customer already exists';
/* Updating status of the staging table for the record.*/
UPDATE xxtr_ksk_cust_data
SET status = 'Rejected'
, description:='Customer already exists'
WHERE cust_number = cur_cust_data_rec.cust_number;


END IF;
END;
END LOOP; --End of Cursor-For loop.
COMMIT;

IF (lv_count <> 0)
THEN /*Submiting the program for customer interface.*/
lv_req_id :=
fnd_request.submit_request (application => 'AR',
program => 'RACUST',
argument1 => 'N'
);
END IF;

IF (lv_req_id > 0)
THEN
fnd_file.put_line (fnd_file.LOG,
'concurrent program is successfully submitted'
);
ELSE
fnd_file.put_line (fnd_file.LOG,
'concurrent program is not submitted'
);
END IF;
END xxtr_ksk_cust_insert; --End of procedure.
END xxtr_ksk_cust_pkg; --End of Package body.
/

Back End Subbmission

Creating and Submitting the Concurrent Program Backend
1. Creating Executable Back end:
BEGIN
fnd_program.executable ('Executable Name',
'Application Name',
'Executable Short Name',
'Description',
'Execution Method',
'Execution File Name'
);
END;
commit;
BEGIN
fnd_program.delete_executable ('Executable Short Name',
'Application Name');
END;
commit;
Example:
BEGIN
fnd_program.executable ('xxmail',
'XXMZ Custom',
'XXMAIL_SH',
'MAIL SENDING',
'Host',
'XXMAIL'
);
END;
BEGIN
fnd_program.delete_executable ('XXMAIL_SH', 'XXMZ Custom');
END;
2. Creating Concurrent Program Back End:
BEGIN
fnd_program.REGISTER ('Program Name',
'Application Name',
'Enabled Flag(Y/N)',
'Short_Name for Program',
'Executable_Short_Name',
'Executable_Application_Name',
'Output_Type(TEXT/PDF/XML/--)'
);
END;
commit;
BEGIN
fnd_program.delete_program('Program Short Name', 'Application Name');
END;
commit;
Example:
BEGIN
fnd_program.REGISTER (program => 'XXMAIL_P',
application =>'XXMZ Custom',
enabled => 'Y',
short_name => 'XXMAIL_PH',
executable_short_name => 'XXMAIL_SH',
executable_application => 'XXMZ Custom',
output_type => 'TEXT'
);
END;
commit;
BEGIN
fnd_program.delete_program ('XXMAIL_PH', 'XXMZ Custom');
END;
commit;
To find user_id,Responsibility_id,resp_application_id
SELECT fur.user_id, fur.responsibility_id,
fur.responsibility_application_id
FROM fnd_user_resp_groups fur,
fnd_user fu,
fnd_application fav,
fnd_responsibility_tl frt
WHERE fu.user_id = fur.user_id
AND fur.responsibility_application_id = fav.application_id
AND frt.responsibility_id = fur.responsibility_id
AND fu.user_id = frt.created_by
AND fav.application_short_name = 'SQLAP'//SHORTNAME OF THE APPLICATION
AND fu.user_name = 'OPERATIONS' //USER NAME
3. Submitting the Concurrent Program Back end
CREATE OR REPLACE PROCEDURE conc_proc
IS
l_request_id NUMBER;
BEGIN
fnd_global.apps_initialize (user_id => 1318,
resp_id => 50554,
resp_appl_id => 200
);
COMMIT;
l_request_id :=
fnd_request.submit_request (application => 'SQLAP',
program => 'XXMAIL_P'
);
COMMIT;
IF l_request_id != 0
THEN
DBMS_OUTPUT.put_line('program submitted successfully::'
|| l_request_id
);
-- Fnd_File.put_line ( Fnd_File.LOG,'Program succesfully
submitteed:'l_request_id );
ELSE
DBMS_OUTPUT.put_line('hai the program notsubmitted
successfully::'|| l_request_id);
--Fnd_File.Put_line(Fnd_File.LOG,'Program Not succesfully submitteed
There are some errors:'l_request_id);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception Raised:');
--Fnd_File.Put_line(Fnd_File.LOG,'Exception raised at the time of
submission');
--END;
END conc_proc;
--end;
/
***hari bodela***