Bharat Oracle Apps Technical

Thursday, 26 September 2013

Requisition import and po aoutocreation using api

CREATE OR REPLACE PACKAGE BODY APPS.xxspanvi_req_po_load
IS
PROCEDURE MAIN (errbuf OUT varchar2,
retcode OUT number,
p_batch_id number)
IS
CURSOR c_requistion_rec IS
SELECT *
FROM
xxspanv_po_req_preinteface
WHERE batch_id=p_batch_id
and batch_id not in (select substr(attribute14,12) from po_requisition_lines_all);

l_error_flag varchar2 (1);
l_error_msg varchar2 (1000);
l_record_id number ;
l_item_id number ;
l_emp_id number ;
l_location_id number ;
l_organization_id number;
l_need_by_date date;
l_org_id number;
l_vendor_id number;
l_site_id number;
l_requestor_id number;
l_chanrge_account_id number;
--l_request_stat varchar2 (10);
BEGIN

BEGIN

SELECT NVL(MAX(record_id),0)
INTO l_record_id
FROM xxspanv_po_req_preinteface
WHERE batch_id=p_batch_id;

UPDATE xxspanv_po_req_preinteface
SET record_id=l_record_id+ROWNUM;

COMMIT;

END;

fnd_file.put_line(fnd_file.log,'Validation started for stageging table');

FOR v_requistion_rec IN c_requistion_rec LOOP
----initializtion local varibale to null-----------
l_error_flag := null;
l_error_msg := null;
l_item_id :=null ;
l_emp_id :=null;
l_location_id :=null;
l_organization_id :=null;
l_need_by_date :=null;
l_org_id :=null;
l_vendor_id :=null;
l_site_id :=null;
l_requestor_id :=null;
l_chanrge_account_id :=null;

-----------VALIDATION STARTS HERE------------------

IF v_requistion_rec.INTERFACE_SOURCE_CODE IS NULL THEN
l_error_flag := 'E';
l_error_msg := 'INTERFACE_SOURCE_CODE Can not be Null';
END IF;

IF v_requistion_rec.requisition_type IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'REQUISITION_TYPE Can not be Null';
END IF;

IF v_requistion_rec.destination_type_code IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DESTINATION_TYPE_CODE Can not be Null';
END IF;

IF v_requistion_rec.item_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'ITEM_NAME Can not be Null';

ELSE

BEGIN
SELECT DISTINCT inventory_item_id
INTO l_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1)=UPPER(v_requistion_rec.item_name);

EXCEPTION WHEN others THEN
-- l_item_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'error occure while retreiving ITEM id'||SQLERRM;
END;

END IF;

IF v_requistion_rec.quantity IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'QUANTITY Can not be Null';
END IF;

IF v_requistion_rec.preparer_full_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'PREPARER_FULL_NAME Can not be Null';

ELSE
BEGIN

SELECT DISTINCT per.person_id
INTO l_emp_id
FROM per_all_people_f per,
per_all_assignments_f paaf
WHERE UPPER(per.full_name)=UPPER(v_requistion_rec.preparer_full_name )
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND per.person_id=paaf.person_id;

EXCEPTION WHEN others THEN
--l_emp_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving emp_id'||SQLERRM;
END;

END IF;

IF v_requistion_rec.uom_code IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'PREPARER_FULL_NAME Can not be Null';
END IF;

IF v_requistion_rec.destination_organization IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DESTINATION_ORGANIZATION Can not be Null';

ELSE
BEGIN

SELECT organization_id
INTO l_organization_id
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE UPPER(ORGANIZATION_NAME)=UPPER(v_requistion_rec.DESTINATION_ORGANIZATION);

EXCEPTION WHEN OTHERS THEN
--l_organization_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Eroor occure while retreiving inventory org_id'||SQLERRM;
END;

END IF;

IF v_requistion_rec.deliver_to_location IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DELIVER_TO_LOCATION can not be null';
ELSE
BEGIN
SELECT location_id
INTO l_location_id
FROM HR_LOCATIONS
WHERE UPPER(location_code)=UPPER(v_requistion_rec.deliver_to_location);

EXCEPTION WHEN OTHERS THEN
--l_location_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving the deliver_to_location_id'||SQLERRM;
END ;

END IF;

IF v_requistion_rec.deliver_to_requestor IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'DELIVER_TO_REQUESTOR can not be null';

ELSE
BEGIN

SELECT per.person_id
INTO l_requestor_id
FROM per_all_people_f per,
per_all_assignments_f paaf
WHERE UPPER(per.full_name)=UPPER(v_requistion_rec.deliver_to_requestor)
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND per.person_id=paaf.person_id;

EXCEPTION WHEN others THEN
--l_emp_id:=NULL
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retreiving requestor_id'||SQLERRM;
END;

END IF;

IF v_requistion_rec.charge_account IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'CHARGE_ACCOUNT can not be null';
ELSE
BEGIN

SELECT DISTINCT code_combination_id
INTO
l_chanrge_account_id
FROM gl_code_combinations_kfv a,
GL_SETS_OF_BOOKS gl
WHERE concatenated_segments=v_requistion_rec.charge_account
AND gl.chart_of_accounts_id=a.chart_of_accounts_id
AND gl.set_of_books_id=fnd_profile.value('gl_set_of_bks_id') ;
EXCEPTION WHEN OTHERS THEN
--l_chanrge_account_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the charge_account_id'||SQLERRM;
END;
END IF;

IF v_requistion_rec.need_by_date IS NULL THEN
l_need_by_date:= SYSDATE+1;
ELSE l_need_by_date:=v_requistion_rec.need_by_date;
END IF;

IF v_requistion_rec.ou_name IS NULL THEN

l_error_flag := 'E';
l_error_msg := l_error_msg||'OU_NAME can not be null';
ELSE

BEGIN

SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE UPPER(NAME)=UPPER(v_requistion_rec.ou_name);
EXCEPTION WHEN OTHERS THEN
--l_org_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the org_id'||SQLERRM;
END;
END IF;

IF v_requistion_rec.unit_price IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'UNIT_PRICE can not be null';
END IF;

IF v_requistion_rec.suggested_vendor_name IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'SUGGESTED_VENDOR_NAME can not be null';
ELSE
BEGIN

SELECT vendor_id
INTO
l_vendor_id
FROM AP_SUPPLIERS
WHERE UPPER(vendor_name)=UPPER(v_requistion_rec.suggested_vendor_name);
EXCEPTION WHEN OTHERS
THEN
--l_vendor_id:=NULL;
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the vendor_id'||SQLERRM;
END;
END IF;

IF v_requistion_rec.suggested_vendor_site IS NULL THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'SUGGESTED_VENDOR_SITE can not be null';
ELSE
BEGIN
SELECT vendor_site_id
INTO l_site_id
FROM ap_supplier_sites
WHERE UPPER(vendor_site_code)=UPPER(v_requistion_rec.suggested_vendor_site)
AND vendor_id=l_vendor_id
AND org_id=l_org_id;
EXCEPTION WHEN OTHERS THEN
l_error_flag := 'E';
l_error_msg := l_error_msg||'Error occure while retrieving the site_id'||SQLERRM;
END;

END IF;

-------VALIDATION ENDS HERE------------------
------STRTING THE INSERTION IN REQUSITION INTERFACE -------------------------
IF l_error_flag IS NULL THEN

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(batch_id
,TRANSACTION_ID
,interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
--,category_id
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,charge_account_id
,need_by_date
,org_id
,unit_price
,autosource_flag
,line_attribute13
,line_attribute14
,suggested_vendor_id
,suggested_vendor_site_id
)
values
(v_requistion_rec.batch_id
,PO_REQUISITIONS_INTERFACE_S.nextval
,'FUTURE'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,l_item_id
,v_requistion_rec.item_description
,v_requistion_rec.quantity
,'INCOMPLETE'
,l_emp_id
,v_requistion_rec.uom_code
,l_organization_id
,v_requistion_rec.destination_subinventory
,l_location_id
,l_requestor_id
,l_chanrge_account_id
,l_need_by_date
,l_org_id
,v_requistion_rec.unit_price
,NULL
,v_requistion_rec.record_id
,'REQ_WRAPPER'||p_batch_id
,l_vendor_id
,l_site_id
);
ELSE
UPDATE xxspanv_po_req_preinteface
SET ERROR_FLAG=l_error_flag,
ERROR_MSG =l_error_msg
WHERE batch_id=v_requistion_rec.batch_id
AND record_id =v_requistion_rec.record_id;

END IF;
COMMIT;
END LOOP;
SUBMIT_REQUEST (P_BATCH_ID);
END MAIN;
PROCEDURE SUBMIT_REQUEST (P_BATCH_ID IN NUMBER --,
--STATUS OUT VARCHAR2
)
IS
l_request_id number;
l_max_wait number;
l_phase varchar2(10);
l_status varchar2(10);
l_dev_status varchar2(10);
l_message varchar2(100);
l_interval number;
l_dev_phase varchar2(10);
rphase varchar2(10);
rstatus varchar2(10);
dphase varchar2(10);
dstatus varchar2(10);
message varchar2(100);
call_status boolean ;
wait_status boolean ;
BEGIN
fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
fnd_profile.value('RESP_ID'),
fnd_profile.value('RESP_APPL_ID'),
null,
null
);

l_request_id:= fnd_request.submit_request('PO'
,'REQIMPORT'
,NULL
,NULL
,FALSE
,NULL---arg1
,p_batch_id
,'VENDOR'
,NULL
,NULL
,'Y'
);
COMMIT;

wait_status:=fnd_concurrent.wait_for_request (l_request_id,
60 ,
0,
l_phase ,
l_status ,
l_dev_phase,
l_dev_status,
L_message);

-- call_status :=fnd_concurrent.get_request_status(l_request_id, '', '',
-- rphase,rstatus,dphase,dstatus, message);
fnd_file.put_line(fnd_file.log,'dphase = '||l_dev_phase||'and '||'dstatus ='||l_dev_status) ;

IF UPPER(l_dev_phase)='COMPLETE' AND UPPER(l_dev_status)= 'NORMAL' THEN
dbms_output.put_line ('calling the procuedure to create po');----calling the po_creation procedure
fnd_file.put_line(fnd_file.log,'calling the procuedure to create po');
CREATE_PO (P_BATCH_ID);
END IF;
exception when others
then
fnd_file.put_line(fnd_file.log,'Error occure in procedure submit_request'||SQLERRM);
END SUBMIT_REQUEST;
PROCEDURE CREATE_PO (P_BATCH_ID IN number)
IS
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(5000);
l_num_lines_processed number;
l_document_number po_headers_all.segment1%TYPE;
l_document_id number;
l_msg varchar2(5000);
l_msg_index_out number;
CURSOR po_interface_headers_rec IS
SELECT distinct prl.requisition_header_id
,prl.vendor_id
,prl.vendor_site_id
,prl.attribute14
--,prl.unit_meas_lookup_code
,prl.destination_organization_id
--,prl.org_id
,prh.org_id
--,prl.deliver_to_location_id
,prh.preparer_id
--,prh.authorization_status
,xprp.ship_to_location
,xprp.bill_to_location
--,xprp.item_name
,xprp.suggested_vendor_name
,xprp.suggested_vendor_site
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
xxspanv_po_req_preinteface xprp
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = 'PURCHASE'
AND to_char(xprp.batch_id)=substr(prl.attribute14,12)
--AND to_char(xprp.record_id)=(prl.ATTRIBUTE13)
AND prh.authorization_status='APPROVED'
--AND prh.closed_code<>'FINALLY CLOSED'
AND prd.requisition_line_id = prl.requisition_line_id
AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
AND prl.attribute14='REQ_WRAPPER'||p_batch_id;

CURSOR po_interface_lines_rec (p_requistion_header_id number)
IS
SELECT prl.requisition_header_id
,prl.quantity
,prl.requisition_line_id
,prl.item_id
,prl.unit_price
,prl.line_num
--,prl.vendor_id
--,prl.vendor_site_id
,prl.attribute14
,prl.unit_meas_lookup_code
,prl.destination_organization_id
,prl.org_id
,prl.deliver_to_location_id
,prh.preparer_id
,prh.authorization_status
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = 'PURCHASE'
AND prh.authorization_status='APPROVED'
--AND prh.closed_code<>'FINALLY CLOSED'
AND prd.requisition_line_id = prl.requisition_line_id
AND prl.requisition_header_id=p_requistion_header_id
AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
AND prl.attribute14='REQ_WRAPPER'||p_batch_id ;

BEGIN
DBMS_OUTPUT.PUT_LINE('CREATE PO PROCEDURE STARTS');
fnd_file.put_line (fnd_file.log,'PO CREATE Procedure starts here.');

fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
fnd_profile.value('RESP_ID'),
fnd_profile.value('RESP_APPL_ID'),
null,
null
);
mo_global.SET_POLICY_CONTEXT('S',204);

for i in po_interface_headers_rec
loop
fnd_file.put_line(fnd_file.log,'requistion_header_id = '||i.requisition_header_id);
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
interface_source_code,
process_code,
action,
org_id,
document_type_code,
document_subtype,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location,
bill_to_location,--,
group_code,
style_id
)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
apps.po_headers_interface_s.currval,
'PO',
'PENDING',
'NEW', -- Indicates this is a new document
i.org_id, -- Org id for operating unit Vision Operations
'PO', -- Indicates a standard PO is being imported
'STANDARD',
'USD', -- The currency to be used in the PO
i.preparer_id, -- The ID of the buyer
i.vendor_id, -- Supplier name
i.vendor_site_id, -- Supplier Site
i.ship_to_location, -- Ship to location name
i.bill_to_location,--, -- Bill to location name
'DEFAULT',--'TestPO'
1
);
FOR j IN po_interface_lines_rec (i.requisition_header_id)
LOOP

INSERT INTO po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
--shipment_num,
line_type,
item_id,--item,
unit_of_measure,
quantity,
unit_price,
--ship_to_organization_code,
--ship_to_location,
requisition_line_id
)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
j.line_num,
--1,
'Goods',
j.item_id, -- Item to imported on the PO line
j.unit_meas_lookup_code, -- Unit of Measure
j.quantity,
j.unit_price, -- Line price in specified currency
j.requisition_line_id--'V1', -- Inventory Organization which will receive the shipment
--'V1- New York City'
);
dbms_output.put_line('insertion done in line interface');
fnd_file.put_line(fnd_file.log,'insertion done in line interface');
END LOOP;
commit;
dbms_output.put_line('api_calling');
fnd_file.put_line(fnd_file.log,'api calling');
FND_MSG_PUB.Initialize;

dbms_output.put_line('po_headers_interface_s.currvalis'||po_headers_interface_s.currval);
PO_INTERFACE_S.create_documents (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_batch_id => po_headers_interface_s.currval,
p_req_operating_unit_id => i.org_id,--–p_req_operating_unit_id,
p_purch_operating_unit_id =>i.org_id,--–p_purch_operating_unit_id,
x_document_id => l_document_id,
x_number_lines => l_num_lines_processed,
x_document_number => l_document_number, --— <DBI FPJ>
p_sourcing_k_doc_type => null,
p_conterms_exist_flag => null,
p_document_creation_method => 'AUTOCREATE',
p_orig_org_id => null
);
fnd_file.put_line(fnd_file.log,'api calling done');
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
fnd_file.put_line(fnd_file.log,'l_return_status='||l_return_status);
UPDATE xxspanv_po_req_preinteface a
SET po_num=l_document_number,
error_flag='P',
a.error_msg=null
WHERE a.suggested_vendor_name=i.suggested_vendor_name
AND a.suggested_vendor_site=i.suggested_vendor_site
AND a.batch_id=p_batch_id;

COMMIT;

ELSIF L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN

ROLLBACK;

FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get(p_msg_index => i,
p_data => l_msg_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_msg_index_out);
l_msg := l_msg||'.'|| l_msg_data;
END LOOP;

FND_MSG_PUB.DELETE_MSG();

UPDATE xxspanv_po_req_preinteface a
SET --po_num=l_document_number,
error_flag='E',
a.error_msg=L_MSG
WHERE a.suggested_vendor_name =i.suggested_vendor_name
AND a.suggested_vendor_site =i.suggested_vendor_site
AND a.batch_id=p_batch_id;
--AND a.item_name =i.item_name;

COMMIT;

END IF;

END LOOP;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error occure in procedure create_po'||SQLERRM);
dbms_output.put_line('exception occured'||sqlerrm);
END CREATE_PO;
end xxspanvi_req_po_load;
/

Receiving Open Iterface.

CREATE OR REPLACE PROCEDURE APPS.XXSPANV_REC_INTERFACE (errbuf OUT varchar2 ,
retcode OUT number)
IS
CURSOR rec_header IS
SELECT po_number,
vendor_name
FROM
xxspanv_inv_rec_preinterface;
CURSOR rec_transaction (p_po_number IN varchar2)
IS
SELECT
pha.po_header_id,
pha.org_id,
pla.unit_meas_lookup_code,
pla.quantity,
pla.item_id,
pha.vendor_id,
pla.po_line_id,
plla.line_location_id,
TRUNC(TO_DATE(pha.creation_date,'DD-MON-RRRR')) creation_date,
plla.receiving_routing_id,
pha.segment1,
plla.ship_to_organization_id,
pha.agent_id
--plla.sh--
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla
WHERE
pha.po_header_id=pla.po_header_id
AND plla.po_header_id=pha.po_header_id
AND plla.po_line_id=pla.po_line_id
AND UPPER(pha.AUTHORIZATION_STATUS)='APPROVED'
AND pha.ORG_ID=204
AND pla.item_id IS NOT NULL
AND pha.segment1=(p_po_number)
AND TRUNC(pha.creation_date) BETWEEN ('01-SEP-13')AND ('30-SEP-13')
AND pla.QUANTITY IS NOT NULL
order by pha.po_header_id;
l_autotransact_code rcv_headers_interface.AUTO_TRANSACT_CODE%type;
BEGIN

fnd_file.put_line(fnd_file.log,'Inserting into rcv_headers_interface');
FOR I IN rec_header
LOOP
INSERT INTO RCV_HEADERS_INTERFACE
(header_interface_id ,
group_id ,
processing_status_code ,
receipt_source_code ,
transaction_type ,
last_update_date ,
last_updated_by ,
last_update_login ,
VENDOR_NAME,
expected_receipt_date ,
validation_flag
)
values (rcv_headers_interface_s.NEXTVAL ,
rcv_interface_groups_s.NEXTVAL ,
'PENDING',
'VENDOR' ,
'NEW' ,
SYSDATE ,
fnd_profile.value('USER_ID'),
fnd_global.LOGIN_ID,
i.vendor_name,
SYSDATE ,
'Y'
);
FOR j IN rec_transaction (i.po_number)
LOOP
l_autotransact_code:=NULL;
IF j.receiving_routing_id in (1,3) then
l_autotransact_code:='RECEIVE';
ELSE l_autotransact_code:='DELIVER';
END IF;

INSERT INTO RCV_TRANSACTIONS_INTERFACE

(interface_transaction_id ,
group_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
transaction_type ,
transaction_date ,
processing_status_code ,
processing_mode_code ,
transaction_status_code ,
po_header_id,
po_line_id ,
item_id ,
quantity ,
unit_of_measure ,
po_line_location_id ,
auto_transact_code ,
receipt_source_code ,
to_organization_id ,
source_document_code ,
header_interface_id ,
validation_flag,
employee_id
)
VALUES (rcv_transactions_interface_s.NEXTVAL ,
rcv_interface_groups_s.CURRVAL ,
SYSDATE,
fnd_profile.VALUE('USER_ID') ,
SYSDATE,
fnd_profile.VALUE('USER_ID'),
fnd_global.login_id,
'RECEIVE',
sysdate,
'PENDING',
'BATCH',
'PENDING',
j.po_header_id,
j.po_line_id,
j.item_id,
j.quantity,
j.unit_meas_lookup_code,
j.line_location_id,
'RECEIVE',--l_autotransact_code,
'VENDOR',
j.ship_to_organization_id,
'PO',
rcv_headers_interface_s.CURRVAL ,
'Y',
j.agent_id
) ;
END LOOP;
END LOOP;
commit;

EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'error occure in XXSPANV_REC_INTERFACE due to : '||SQLERRM);
END;
/

Need to run Receiving Transaction Processor to populate rcv_transactions.

Then run Rec

Monday, 10 June 2013

Query To Find Concurrent Program Attached To a Responsibility

SELECT frt.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM fnd_Responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND fcpt.user_concurrent_program_name = '&conc_program_name'
ORDER BY 1,2,3,4

Friday, 14 September 2012

Good Blog Link For Fusion

http://oracleapps88.blogspot.in/search/label/FUSION

Interface And Conversion Concept In Oracle Apps

How conversion and interface differ?

There are good numbers of parameter on which they can be categorize. Take few of them:

Frequency
- conversions are a one time event
- interfaces are ongoing
Occurrence in the project timeline
- conversions executed before production
- interfaces executed during production
Manner of execution
- conversions are batch
- interfaces may be batch or real time
Complexity
- Conversion does have very complex, its totally depends upon the data mapping activity.
- coordinating with other systems make interfaces more complex
Maintenance
- Maintence of interface is bit cost intensive task.

Interface Type
You have learned how interface is differ from Conversion/Migration. Now lets take few types of interfaces:
Normally in any system , there are two kinds of interface as:

Inbound Interfaces

An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables.
A typical inbound interface would follow these steps:

Extract data from legacy system into a flat file.
Use SQL*Loader or equivalent tool to upload information into a temporary table.
Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables.
Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.

Outbound Interfaces
o An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file).
o A typical outbound interface would follow these steps:
- Write a PL/SQL program to extract data from Oracle base tables into a flat file.
- Use a custom program to read that data and post it into the legacy system

Do we have some other standard way to do interface?

Open Interface is a table based interface registered as a concurrent program
- process records in batches.
- spawned(Pro-C) or PL/SQL based programs.
API (Application Program Interface) is a parameter based stored procedure
- directly impacts base database tables.
- may be called from Oracle open interfaces,Forms, Reports.
EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO's, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.(
Enterprise Application Integration (EAI) solutions are often used when complex integration requirements exist.

What Is An Open Interface Table (OIT)?

For inbound interfaces, the interface table is the intermediary table where data from the source application temporarily resides until it is validated and processed into an Oracle base table through a standard import concurrent program.
Open Interface Tables are standard Oracle tables.
- Oracle uses OITs to provide a simple interface to Oracle base tables.
- Oracle has list of all the open interface that oracle offered in there product.

Oracle Interface Program

Most Oracle modules have standard import programs (concurrent processes) to facilitate custom inbound interfaces. The specific processing performed varies by application.
These programs pull data from the open interface tables, validate the data, and then insert into one or more Oracle base tables.
Upon successful completion of processing, the program deletes the processed rows from the interface table or marks them as completed.
Depending on the import, errors can be viewed in various ways (exception reports, error tables, forms, etcâ€¦).

Examples of standard import programs:

GL: Journal Import
AP: Payables Open Interface
AR: Customer Interface
INV : Item Import
AR - Autoinvoice

Package to Get Account Concatenated Description In Oracle Apps

GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION CHART_ACCOUNT_ID,CODE_OMBINATION_ID,SEGMENT_VALUE)