PROCEDURE xxx1_xxx_release_holds_proc(p_errbuf IN OUT VARCHAR2
,p_retcode IN OUT VARCHAR2)
IS
/****************************************************************************
* Filename : xxx1_xxx_release_holds_proc.sql
* Description : This package fetch hold data from base tables and release that holds.
*
* Author Date Version Description
* Sriaknth Yarram 06-Jun-2014 1.00 Created
****************************************************************************/
-------local variable declaration----
v_return_status VARCHAR2 (30);
v_msg_data VARCHAR2 (4000);
v_msg_count NUMBER;
v_hold_source_rec oe_holds_pvt.hold_source_rec_type;
v_order_tbl oe_holds_pvt.order_tbl_type;
v_hold_release_rec oe_holds_pvt.hold_release_rec_type;
v_hold_id NUMBER;
v_header_id NUMBER;
v_line_id NUMBER;
v_context VARCHAR2 (2);
v_user_id NUMBER := 3603;
v_resp_id NUMBER := 51424;
v_appl_id NUMBER := 660;
v_err_flag VARCHAR2 (1) := NULL;
v_err_message VARCHAR2 (1000);
v_lookup_code oe_lookups.lookup_code%TYPE;
v_hold_entity_id NUMBER;
v_hold_release_id NUMBER;
v_hold_source_id NUMBER;
l_user_exception EXCEPTION;
j NUMBER := 0;
-------------Cursor to fetch the data from the custom table
CURSOR cur_hold_source_dtl
IS
select hs.hold_source_id, hs.hold_entity_id, hs.hold_entity_id2,ooha.header_id,oh.org_id,ooha.order_number,ota.order_category_code,hd.name Hold_Name,hd.hold_id
from apps.oe_transaction_types_tl ott
,apps.oe_transaction_types_all ota
,apps.oe_order_headers_all ooha
,apps.oe_order_holds_all oh
,apps.oe_hold_sources_all hs
,apps.oe_hold_definitions hd
where (ott.name like '%Transaction Types%'
or ott.name like '%transaction Types%')
and ott.transaction_type_id = ota.transaction_type_id
--and ota.order_category_code = 'ORDER'
and ooha.ORDER_TYPE_ID = ott.TRANSACTION_TYPE_ID
and ooha.ORDER_TYPE_ID = ota.TRANSACTION_TYPE_ID
and ooha.flow_status_code in('ENTERED','BOOKED')
AND oh.header_id= ooha.header_id
AND hs.hold_source_id=oh.hold_source_id
and hs.hold_id = hd.hold_id
AND hs.released_flag = 'N'
and ooha.org_id=84--TMSE SALES
and hd.name like 'Credit Check%'
and ota.order_category_code !='RETURN';
TYPE t_bulk_hold IS TABLE OF cur_hold_source_dtl%ROWTYPE;
b_hold t_bulk_hold;
BEGIN
fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);
fnd_file.put_line (fnd_file.LOG,'user_id is'||v_user_id);
fnd_file.put_line (fnd_file.LOG,'resp_id is'||v_resp_id);
fnd_file.put_line (fnd_file.LOG,'appl id'||v_appl_id);
mo_global.set_policy_context('S',84);
---------- fetch the cursor data into the local bulk variable
OPEN cur_hold_source_dtl;
FETCH cur_hold_source_dtl
BULK COLLECT INTO b_hold;
CLOSE cur_hold_source_dtl;
BEGIN
----------- Close the Cursor
FOR indx IN b_hold.FIRST .. b_hold.LAST
LOOP
j:= j+1;
v_err_flag := 'Y';
v_err_message := NULL;
v_header_id := NULL;
v_line_id := NULL;
BEGIN
SELECT lookup_code
INTO v_lookup_code
FROM apps.oe_lookups
WHERE UPPER (meaning) = UPPER ('Credit Hold')
AND lookup_type = 'RELEASE_REASON';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_err_flag := 'E';
v_err_message :=
v_err_message || 'Not a valid release reason code';
fnd_file.put_line (fnd_file.LOG, v_err_message);
WHEN OTHERS
THEN
v_err_flag := 'E';
v_err_message := v_err_message || SQLERRM;
fnd_file.put_line (fnd_file.LOG, v_err_message);
END;
------------------------Calling the API to Release the HOLD for the sales order
v_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
v_hold_release_rec := oe_holds_pvt.g_miss_hold_release_rec;
v_hold_source_rec.hold_source_id :=
b_hold (indx).hold_source_id;
v_hold_release_rec.hold_source_id :=
b_hold (indx).hold_source_id;
v_hold_release_rec.release_reason_code := v_lookup_code;
oe_holds_pub.release_holds
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_hold_source_rec => v_hold_source_rec,
p_hold_release_rec => v_hold_release_rec,
--p_check_authorization_flag => 'N',
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
fnd_file.put_line (fnd_file.LOG,'A.1, release_holds API status is-'||v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
v_err_flag := 'S';
v_err_message :=
'Hold Released Successfully for this order number =';
fnd_file.put_line (fnd_file.LOG, '' || v_err_message || b_hold (indx).order_number );
fnd_file.put_line (fnd_file.LOG, 'Start to load the data into custom table XXX1_XXX_TEMP_HOLDS');
-- fnd_file.put_line (fnd_file.LOG, 'The Order Number =' || b_hold (indx).order_number);
Insert Into apps.XXX1_XXX_TEMP_HOLDS(hold_source_id,hold_entity_id,header_id,org_id,order_number,order_category_code,Hold_Name,hold_id)
Values(b_hold (indx).hold_source_id,b_hold (indx).hold_entity_id,b_hold (indx).header_id,b_hold (indx).org_id,
b_hold (indx).order_number,b_hold (indx).order_category_code,b_hold (indx).Hold_Name,b_hold (indx).hold_id);
---fnd_file.put_line (fnd_file.LOG, '' || v_err_message);
ELSIF v_return_status IS NULL
THEN
v_err_flag := 'E';
v_err_message :=
v_err_message || 'Status is null' || '--' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, '' || v_err_message);
ELSE
v_err_flag := 'E';
v_err_message :=
v_err_message || 'Failed' || '---' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, '' || v_err_message);
END IF;
END LOOP;
fnd_file.put_line (fnd_file.LOG,'Number of records processed: '||to_char(j));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'We do not have hold orders');
fnd_file.put_line (fnd_file.LOG,'Number of records processed: '||to_char(j));
END;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END xxx1_xxx_release_holds_proc ;
PROCEDURE xxx1_xxx_apply_holds_proc(p_errbuf IN OUT VARCHAR2
,p_retcode IN OUT VARCHAR2)
IS
/****************************************************************************
* Filename : xxx1_xxx_apply_holds_proc.sql
* Description : This package was apply the holds.
*
* Author Date Version Description
* Sriaknth Yarram 06-Jun-2014 1.00 Created
****************************************************************************/
-------------Cursor to fetch the data from the Order lines table
CURSOR c_hold_so
IS
select oeh.order_number,oeh.header_id,xct.hold_id,oeh.org_id
from oe_order_headers_all oeh
,hz_parties hp
,hz_cust_accounts hca
,hr_operating_units hou
,hr_operating_units hou1
,po_requisition_headers_all prh
,(select distinct
requisition_header_id
,note_to_agent
from po_requisition_lines_all) prl
,oe_order_headers_all oeh1
,oe_transaction_types_tl ott1
,apps.XXX1_xxx_TEMP_HOLDS xct
--,apps.oe_order_lines_all oola
where oeh.org_id = hou.organization_id
and oeh1.order_number = xct.order_number
and oeh.order_source_id = 10 -- Internal
---and oeh1.order_number='12345'
and hou.name = operating Unit
and hou1.name =operating Unit
and oeh.orig_sys_document_ref = prh.segment1
and prh.requisition_header_id = prl.requisition_header_id
and substr(prl.note_to_agent,instr(prl.note_to_agent,':')+1,
instr(prl.note_to_agent,',')-instr(prl.note_to_agent,':')-1) = to_char(oeh1.order_number)
and oeh1.order_type_id = ott1.transaction_type_id
and oeh1.sold_to_org_id = hca.cust_account_id
and hca.party_id = hp.party_id
and oeh1.org_id = hou1.organization_id
-- and oeh.header_id= oola.header_id
--AND oola.shippable_flag = 'Y'
-- AND oola.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
AND oeh.flow_status_code <> 'CLOSED'
AND oeh.header_id not IN (
SELECT a.header_id
FROM oe_order_holds_all a,
oe_hold_sources_all b,
oe_hold_definitions c
WHERE a.header_id = oeh.header_id
AND a.hold_source_id = b.hold_source_id
AND b.hold_id = c.hold_id
AND c.NAME like 'Cred%C%'
)
order by oeh.ordered_date desc;
-------local variable declaration----
v_return_status VARCHAR2 (30);
v_msg_data VARCHAR2 (4000);
v_msg_count NUMBER;
v_hold_source_rec oe_holds_pvt.hold_source_rec_type;
v_order_tbl oe_holds_pvt.order_tbl_type;
v_hold_id NUMBER;
v_hold_entity_code VARCHAR2 (10) DEFAULT 'O';
v_header_id NUMBER;
v_line_id NUMBER;
v_context VARCHAR2 (2);
v_user_id NUMBER := 3603 ;
v_resp_id NUMBER := 50815;
v_appl_id NUMBER := 660;
v_err_flag VARCHAR2 (2);
v_err_message VARCHAR2 (1000);
v_org_id hr_operating_units.organization_id%TYPE;
j NUMBER := 0;
TYPE t_bulk_hold IS TABLE OF c_hold_so%ROWTYPE;
b_hold t_bulk_hold;
BEGIN
fnd_file.put_line(fnd_file.output,'user_id is'||v_user_id);
fnd_file.put_line(fnd_file.output,'resp_id is'||v_resp_id);
fnd_file.put_line(fnd_file.output,'appl id'||v_appl_id);
fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);
---------- fetch the cursor data into the local bulk variable ---
OPEN c_hold_so;
FETCH c_hold_so
BULK COLLECT INTO b_hold;
CLOSE c_hold_so;
----------- Close the Cursor --------
FOR indx IN 1 .. b_hold.COUNT
LOOP
j:= j+1;
v_org_id := b_hold (indx).org_id;
mo_global.set_policy_context ('S',v_org_id);
v_err_flag := 'N';
v_err_message := NULL;
v_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
v_hold_source_rec.hold_id := b_hold (indx).hold_id;
v_hold_source_rec.hold_entity_code := v_hold_entity_code;
v_hold_source_rec.hold_entity_id := b_hold (indx).header_id;
v_hold_source_rec.header_id := b_hold (indx).header_id;
--v_hold_source_rec.line_id := b_hold (indx).line_id;
oe_holds_pub.apply_holds (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_hold_source_rec => v_hold_source_rec,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
v_err_flag := 'S';
v_err_message := 'Hold Applied Successfully for this';
ELSIF v_return_status IS NULL
THEN
v_err_flag := 'E';
v_err_message :=
v_err_message || 'Status is null' || '--' || SQLERRM;
ELSE
v_err_flag := 'E';
v_err_message := v_err_message || 'Failed' || '---' || SQLERRM;
END IF;
/*fnd_file.put_line(fnd_file.output,
'Status - '
|| b_hold (indx).order_number
|| ':'
|| b_hold (indx).header_id
|| ':'
|| b_hold (indx).line_id
|| v_err_message
); */
fnd_file.put_line(fnd_file.log,v_err_message || b_hold (indx).order_number
);
END LOOP;
--- dbms_output.put_line('Number of records processed: '||to_char(j));
fnd_file.put_line (fnd_file.LOG,'Number of records processed: '||to_char(j));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'We do not have orders');
fnd_file.put_line (fnd_file.LOG,'Number of records processed: '||to_char(j));
END xxx1_xxx_apply_holds_proc;
,p_retcode IN OUT VARCHAR2)
IS
/****************************************************************************
* Filename : xxx1_xxx_release_holds_proc.sql
* Description : This package fetch hold data from base tables and release that holds.
*
* Author Date Version Description
* Sriaknth Yarram 06-Jun-2014 1.00 Created
****************************************************************************/
-------local variable declaration----
v_return_status VARCHAR2 (30);
v_msg_data VARCHAR2 (4000);
v_msg_count NUMBER;
v_hold_source_rec oe_holds_pvt.hold_source_rec_type;
v_order_tbl oe_holds_pvt.order_tbl_type;
v_hold_release_rec oe_holds_pvt.hold_release_rec_type;
v_hold_id NUMBER;
v_header_id NUMBER;
v_line_id NUMBER;
v_context VARCHAR2 (2);
v_user_id NUMBER := 3603;
v_resp_id NUMBER := 51424;
v_appl_id NUMBER := 660;
v_err_flag VARCHAR2 (1) := NULL;
v_err_message VARCHAR2 (1000);
v_lookup_code oe_lookups.lookup_code%TYPE;
v_hold_entity_id NUMBER;
v_hold_release_id NUMBER;
v_hold_source_id NUMBER;
l_user_exception EXCEPTION;
j NUMBER := 0;
-------------Cursor to fetch the data from the custom table
CURSOR cur_hold_source_dtl
IS
select hs.hold_source_id, hs.hold_entity_id, hs.hold_entity_id2,ooha.header_id,oh.org_id,ooha.order_number,ota.order_category_code,hd.name Hold_Name,hd.hold_id
from apps.oe_transaction_types_tl ott
,apps.oe_transaction_types_all ota
,apps.oe_order_headers_all ooha
,apps.oe_order_holds_all oh
,apps.oe_hold_sources_all hs
,apps.oe_hold_definitions hd
where (ott.name like '%Transaction Types%'
or ott.name like '%transaction Types%')
and ott.transaction_type_id = ota.transaction_type_id
--and ota.order_category_code = 'ORDER'
and ooha.ORDER_TYPE_ID = ott.TRANSACTION_TYPE_ID
and ooha.ORDER_TYPE_ID = ota.TRANSACTION_TYPE_ID
and ooha.flow_status_code in('ENTERED','BOOKED')
AND oh.header_id= ooha.header_id
AND hs.hold_source_id=oh.hold_source_id
and hs.hold_id = hd.hold_id
AND hs.released_flag = 'N'
and ooha.org_id=84--TMSE SALES
and hd.name like 'Credit Check%'
and ota.order_category_code !='RETURN';
TYPE t_bulk_hold IS TABLE OF cur_hold_source_dtl%ROWTYPE;
b_hold t_bulk_hold;
BEGIN
fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);
fnd_file.put_line (fnd_file.LOG,'user_id is'||v_user_id);
fnd_file.put_line (fnd_file.LOG,'resp_id is'||v_resp_id);
fnd_file.put_line (fnd_file.LOG,'appl id'||v_appl_id);
mo_global.set_policy_context('S',84);
---------- fetch the cursor data into the local bulk variable
OPEN cur_hold_source_dtl;
FETCH cur_hold_source_dtl
BULK COLLECT INTO b_hold;
CLOSE cur_hold_source_dtl;
BEGIN
----------- Close the Cursor
FOR indx IN b_hold.FIRST .. b_hold.LAST
LOOP
j:= j+1;
v_err_flag := 'Y';
v_err_message := NULL;
v_header_id := NULL;
v_line_id := NULL;
BEGIN
SELECT lookup_code
INTO v_lookup_code
FROM apps.oe_lookups
WHERE UPPER (meaning) = UPPER ('Credit Hold')
AND lookup_type = 'RELEASE_REASON';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_err_flag := 'E';
v_err_message :=
v_err_message || 'Not a valid release reason code';
fnd_file.put_line (fnd_file.LOG, v_err_message);
WHEN OTHERS
THEN
v_err_flag := 'E';
v_err_message := v_err_message || SQLERRM;
fnd_file.put_line (fnd_file.LOG, v_err_message);
END;
------------------------Calling the API to Release the HOLD for the sales order
v_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
v_hold_release_rec := oe_holds_pvt.g_miss_hold_release_rec;
v_hold_source_rec.hold_source_id :=
b_hold (indx).hold_source_id;
v_hold_release_rec.hold_source_id :=
b_hold (indx).hold_source_id;
v_hold_release_rec.release_reason_code := v_lookup_code;
oe_holds_pub.release_holds
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_hold_source_rec => v_hold_source_rec,
p_hold_release_rec => v_hold_release_rec,
--p_check_authorization_flag => 'N',
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
fnd_file.put_line (fnd_file.LOG,'A.1, release_holds API status is-'||v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
v_err_flag := 'S';
v_err_message :=
'Hold Released Successfully for this order number =';
fnd_file.put_line (fnd_file.LOG, '' || v_err_message || b_hold (indx).order_number );
fnd_file.put_line (fnd_file.LOG, 'Start to load the data into custom table XXX1_XXX_TEMP_HOLDS');
-- fnd_file.put_line (fnd_file.LOG, 'The Order Number =' || b_hold (indx).order_number);
Insert Into apps.XXX1_XXX_TEMP_HOLDS(hold_source_id,hold_entity_id,header_id,org_id,order_number,order_category_code,Hold_Name,hold_id)
Values(b_hold (indx).hold_source_id,b_hold (indx).hold_entity_id,b_hold (indx).header_id,b_hold (indx).org_id,
b_hold (indx).order_number,b_hold (indx).order_category_code,b_hold (indx).Hold_Name,b_hold (indx).hold_id);
---fnd_file.put_line (fnd_file.LOG, '' || v_err_message);
ELSIF v_return_status IS NULL
THEN
v_err_flag := 'E';
v_err_message :=
v_err_message || 'Status is null' || '--' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, '' || v_err_message);
ELSE
v_err_flag := 'E';
v_err_message :=
v_err_message || 'Failed' || '---' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, '' || v_err_message);
END IF;
END LOOP;
fnd_file.put_line (fnd_file.LOG,'Number of records processed: '||to_char(j));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'We do not have hold orders');
fnd_file.put_line (fnd_file.LOG,'Number of records processed: '||to_char(j));
END;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END xxx1_xxx_release_holds_proc ;
PROCEDURE xxx1_xxx_apply_holds_proc(p_errbuf IN OUT VARCHAR2
,p_retcode IN OUT VARCHAR2)
IS
/****************************************************************************
* Filename : xxx1_xxx_apply_holds_proc.sql
* Description : This package was apply the holds.
*
* Author Date Version Description
* Sriaknth Yarram 06-Jun-2014 1.00 Created
****************************************************************************/
-------------Cursor to fetch the data from the Order lines table
CURSOR c_hold_so
IS
select oeh.order_number,oeh.header_id,xct.hold_id,oeh.org_id
from oe_order_headers_all oeh
,hz_parties hp
,hz_cust_accounts hca
,hr_operating_units hou
,hr_operating_units hou1
,po_requisition_headers_all prh
,(select distinct
requisition_header_id
,note_to_agent
from po_requisition_lines_all) prl
,oe_order_headers_all oeh1
,oe_transaction_types_tl ott1
,apps.XXX1_xxx_TEMP_HOLDS xct
--,apps.oe_order_lines_all oola
where oeh.org_id = hou.organization_id
and oeh1.order_number = xct.order_number
and oeh.order_source_id = 10 -- Internal
---and oeh1.order_number='12345'
and hou.name = operating Unit
and hou1.name =operating Unit
and oeh.orig_sys_document_ref = prh.segment1
and prh.requisition_header_id = prl.requisition_header_id
and substr(prl.note_to_agent,instr(prl.note_to_agent,':')+1,
instr(prl.note_to_agent,',')-instr(prl.note_to_agent,':')-1) = to_char(oeh1.order_number)
and oeh1.order_type_id = ott1.transaction_type_id
and oeh1.sold_to_org_id = hca.cust_account_id
and hca.party_id = hp.party_id
and oeh1.org_id = hou1.organization_id
-- and oeh.header_id= oola.header_id
--AND oola.shippable_flag = 'Y'
-- AND oola.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
AND oeh.flow_status_code <> 'CLOSED'
AND oeh.header_id not IN (
SELECT a.header_id
FROM oe_order_holds_all a,
oe_hold_sources_all b,
oe_hold_definitions c
WHERE a.header_id = oeh.header_id
AND a.hold_source_id = b.hold_source_id
AND b.hold_id = c.hold_id
AND c.NAME like 'Cred%C%'
)
order by oeh.ordered_date desc;
-------local variable declaration----
v_return_status VARCHAR2 (30);
v_msg_data VARCHAR2 (4000);
v_msg_count NUMBER;
v_hold_source_rec oe_holds_pvt.hold_source_rec_type;
v_order_tbl oe_holds_pvt.order_tbl_type;
v_hold_id NUMBER;
v_hold_entity_code VARCHAR2 (10) DEFAULT 'O';
v_header_id NUMBER;
v_line_id NUMBER;
v_context VARCHAR2 (2);
v_user_id NUMBER := 3603 ;
v_resp_id NUMBER := 50815;
v_appl_id NUMBER := 660;
v_err_flag VARCHAR2 (2);
v_err_message VARCHAR2 (1000);
v_org_id hr_operating_units.organization_id%TYPE;
j NUMBER := 0;
TYPE t_bulk_hold IS TABLE OF c_hold_so%ROWTYPE;
b_hold t_bulk_hold;
BEGIN
fnd_file.put_line(fnd_file.output,'user_id is'||v_user_id);
fnd_file.put_line(fnd_file.output,'resp_id is'||v_resp_id);
fnd_file.put_line(fnd_file.output,'appl id'||v_appl_id);
fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);
---------- fetch the cursor data into the local bulk variable ---
OPEN c_hold_so;
FETCH c_hold_so
BULK COLLECT INTO b_hold;
CLOSE c_hold_so;
----------- Close the Cursor --------
FOR indx IN 1 .. b_hold.COUNT
LOOP
j:= j+1;
v_org_id := b_hold (indx).org_id;
mo_global.set_policy_context ('S',v_org_id);
v_err_flag := 'N';
v_err_message := NULL;
v_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
v_hold_source_rec.hold_id := b_hold (indx).hold_id;
v_hold_source_rec.hold_entity_code := v_hold_entity_code;
v_hold_source_rec.hold_entity_id := b_hold (indx).header_id;
v_hold_source_rec.header_id := b_hold (indx).header_id;
--v_hold_source_rec.line_id := b_hold (indx).line_id;
oe_holds_pub.apply_holds (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_hold_source_rec => v_hold_source_rec,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
v_err_flag := 'S';
v_err_message := 'Hold Applied Successfully for this';
ELSIF v_return_status IS NULL
THEN
v_err_flag := 'E';
v_err_message :=
v_err_message || 'Status is null' || '--' || SQLERRM;
ELSE
v_err_flag := 'E';
v_err_message := v_err_message || 'Failed' || '---' || SQLERRM;
END IF;
/*fnd_file.put_line(fnd_file.output,
'Status - '
|| b_hold (indx).order_number
|| ':'
|| b_hold (indx).header_id
|| ':'
|| b_hold (indx).line_id
|| v_err_message
); */
fnd_file.put_line(fnd_file.log,v_err_message || b_hold (indx).order_number
);
END LOOP;
--- dbms_output.put_line('Number of records processed: '||to_char(j));
fnd_file.put_line (fnd_file.LOG,'Number of records processed: '||to_char(j));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'We do not have orders');
fnd_file.put_line (fnd_file.LOG,'Number of records processed: '||to_char(j));
END xxx1_xxx_apply_holds_proc;
