Oracle Apps: January 2012

Saturday, 28 January 2012

Friday, 27 January 2012

MTL Inventory Item Accounts Update Program

Run Item Import Program with the generated Process id from the below program after program is completed successfull.

Get Field
-----

FUNCTION get_field(p_line IN VARCHAR2, p_delimiter IN VARCHAR2, p_field_no IN NUMBER) RETURN VARCHAR2 IS
l_start NUMBER :=0;
l_end NUMBER :=0;
lc_delimiter VARCHAR2(10);
BEGIN
lc_delimiter := p_delimiter;
/*****-- Determine start position--*****/
IF p_field_no = 1 THEN
l_start :=0;
ELSE
l_start :=INSTR(p_line,p_delimiter,1,(p_field_no - 1));
IF l_start = 0 THEN
RETURN NULL;
END IF;
END IF;
/*****-- Determine end position--*****/
l_end :=INSTR(p_line,p_delimiter,1,p_field_no);
IF l_end = 0 THEN
l_end := LENGTH(p_line) + 1;
END IF;
/*****-- Extract the field data --*****/
IF (l_end - l_start) = 1 THEN
RETURN NULL;
ELSE
RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_field;
--------------------------------------------------------------------------------------------------------

PROCEDURE mtl_inv_acct_upload(Errbuf OUT VARCHAR2,Retcode OUT NUMBER,p_file_name IN VARCHAR2,P_LINE_NUM IN NUMBER)
IS
reqfiletype UTL_FILE.FILE_TYPE;
req_line VARCHAR2(3000);
req_delim VARCHAR2(1):=',';
rec_count NUMBER;
v_File_Name VARCHAR2(200):=P_File_Name;
v_date_processed DATE; -- (Q)
ln_user_id NUMBER:=FND_GLOBAL.USER_ID;
ln_resp_id NUMBER:=FND_GLOBAL.RESP_ID;
ln_resp_app_id NUMBER:=FND_GLOBAL.RESP_APPL_ID;
v_request_id NUMBER:=0;
ln_batch_id NUMBER;
Lc_Transaction_type VARCHAR2(500);
ln_line_no NUMBER;
ln_record_id NUMBER;

ln_last_updated_by NUMBER;
ln_conc_req_id NUMBER;
ln_login_id NUMBER;
lc_error_flag VARCHAR2(10);
lc_errors_all VARCHAR2(500);
ln_cnt NUMBER;
lc_error_message VARCHAR2(500);
ln_operating_org_id NUMBER;
lc_status VARCHAR2(50);
ln_org_id NUMBER;
lc_error_msg VARCHAR2(500);
ln_request_id NUMBER;
lc_item_no VARCHAR2(50);
ln_item_id NUMBER;
lc_item_desc VARCHAR2(500);
lc_whse_code VARCHAR2(10);
ln_whse_id NUMBER;
lc_acct_comb VARCHAR2(500);
lc_loc VARCHAR2(10);
ln_rule_id NUMBER;
ln_process_id NUMBER;
ln_ccid NUMBER;
lc_rev_concat_segments VARCHAR2(500);
ln_chart_of_account_id NUMBER;
lc_record_status VARCHAR2(20);
lc_item_sts_code VARCHAR2(20);
lc_item_type VARCHAR2(20);
lc_product_code VARCHAR2(20);
lc_lob VARCHAR2(20);

BEGIN
v_file_name:=p_file_name;
reqfiletype := UTL_FILE.FOPEN('MTL_INV_ACCT_UPD', v_File_Name, 'r');
ln_line_no :=0;

---Process id for import items interface
Begin
Select to_char(sysdate,'DDMMYYYYHH24MISS') into ln_process_id from dual;
End;

LOOP
ln_line_no := ln_line_no + 1;
BEGIN
UTL_FILE.GET_LINE(reqfiletype , req_line);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
IF ln_line_no > p_line_num THEN
---------------------------------------------------------------------------------------------
--- Mapping with csv fields
-----------------------------------------------------------------------------------------------
--Item Code
BEGIN
lc_item_no := TRIM(get_field(req_line,req_delim,1)); -- (A)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Item Number '||SQLERRM);
END;
-----------------------------------------------------------------------------------------------
--Item Description
BEGIN
lc_item_desc := TRIM(get_field(req_line,req_delim,2)); -- (B)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Item Description '||SQLERRM);
END;
-----------------------------------------------------------------
-- Whse Code
BEGIN
lc_whse_code := TRIM(get_field(req_line,req_delim,3)); -- (C)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Whse Code '||SQLERRM);
END;
-----------------------------------------------------------------
-- Account
BEGIN
lc_acct_comb := TRIM(get_field(req_line,req_delim,4)); -- (D)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Acct Comb '||SQLERRM);
END;
-----------------------------------------------------------------
---- Record Status
BEGIN
lc_record_status := TRIM(get_field(req_line,req_delim,5)); -- (E)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Start Period '||SQLERRM);
END;
----------------------------------------------------------------

BEGIN

ln_user_id := apps.fnd_profile.value('USER_ID');

IF ln_user_id IS NULL THEN
ln_user_id := -1;
END IF;
ln_last_updated_by := APPS.FND_GLOBAL.USER_ID;
-- To get the Conc Request ID
ln_conc_req_id := APPS.FND_GLOBAL.CONC_REQUEST_ID;
IF ln_conc_req_id IS NULL THEN
ln_conc_req_id := -1;
END IF;
-- To get the Login ID
ln_login_id := APPS.FND_GLOBAL.LOGIN_ID;
IF ln_login_id IS NULL THEN
ln_login_id := -1;
END IF;
-- To get the Set of Book ID
--ln_set_of_book_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
-- To get the Operating Uni ID
ln_operating_org_id := fnd_profile.VALUE ('ORG_ID');
-- Start to Process the Cursor Records

---Validations for Account update in MTL Item Master

/******************************************************/
-- Validation for organization code
/******************************************************/

Begin
SELECT organization_id into ln_whse_id
FROM mtl_parameters
WHERE organization_code = lc_whse_code;
Exception When Others Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Whse Code Either Does Not Exist or is inactive in Inventory Organization Master -'||lc_whse_code );
Retcode := 2;
Return;
End;

--
/******************************************************/
-- Validation for item code
/******************************************************/

Begin
Select inventory_item_id into ln_item_id
From mtl_system_items_b
Where organization_id = ln_whse_id
And segment1 = lc_item_no;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Item Code - '||ln_item_id||' either does not exist or is inactive in Inventory Item master for warehouse - '||lc_whse_code);
retcode:=2;
Return;
End;

/******************************************************/
-- Product code validation
/******************************************************/

Begin
Select flex_value into lc_product_code
From fnd_flex_values_vl
Where flex_value_set_id = 1005239
And Trim(substr(Description,instr(Description,'-',-1)+1)) = Trim(lc_item_no);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Item Product Code does not exist in key flexfield setup -'||lc_item_no);
retcode:=2;
Return;
End;

/******************************************************/
--Cross Validation checkinf for LOB and PRODUCT
/******************************************************/

Begin
Select rule_line_id into ln_rule_id
From fnd_flex_validation_rule_lines
Where flex_validation_rule_name like 'LOB & Product'
And substr(concatenated_segments_high,3,3) = Substr(lc_acct_comb,6,3)
And substr(concatenated_segments_high,instr(concatenated_segments_high,'.',-2)+1,5) = Substr(lc_acct_comb,25,5);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Cross validation rule does not exist for lob - '||Substr(lc_acct_comb,6,3)||' and product code - '||Substr(lc_acct_comb,25,5));
retcode:=2;
Return;
End;

/******************************************************/
-- Location Validation
/******************************************************/

Begin
Select substr(acctg_unit_no,6) into lc_loc
From gl_accu_mst gat,
gl_accu_map gap
Where gap.acctg_unit_id = gat.acctg_unit_id
And gap.whse_code = lc_whse_code;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Please check LOC in MAC account combination - '||lc_whse_code);
retcode:=2;
Return;
End;
--
If lc_loc <> Substr(lc_acct_comb,10,3) Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Please check LOC in account combination,Either it does not exist in MAC or wrong in sheet - '||lc_whse_code);
retcode:=2;
Return;
End If;

/******************************************************/
-- LOB validation
/******************************************************/

Begin
Select segment5 into lc_lob
From MTL_ITEM_CATEGORIES_V
Where CATEGORY_SET_ID = 1100000021
And inventory_item_id = ln_item_id
And organization_id = 23;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'LOB does not exist in Item Category -'||ln_item_id);
retcode:=2;
Return;
End;
--
IF lc_lob <> Substr(lc_acct_comb,6,3) THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'LOB Mismatch with LOB in Item Category of Inventory Item Master -'||lc_lob||' for item id -'||ln_item_id);
retcode:=2;
Return;
End If;

/******************************************************/
--Account combination creation using API
/******************************************************/

Begin

ln_ccid := fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => 50186,
validation_date => TO_CHAR(SYSDATE,'DD-MON-YYYY'),
concatenated_segments => lc_acct_comb);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Account Code Does Not Exist in Master -'||lc_acct_comb);

retcode:=2;
Return;
End;
--Cross validation failure check
If ln_ccid = 0 Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Account Code Cannot be created please check cross validation rules -'||lc_acct_comb);
retcode:=2;
Return;
End If;

-- End of Vlidations for sales Account creation in Inventory Item Master

IF ln_item_id > 0 Then
fnd_file.put_line(fnd_file.log,'In If Before Insert of interface -'||lc_item_no||' and warehouse - '||lc_whse_code);

INSERT INTO mtl_system_items_interface( inventory_item_id
, organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, segment1
, process_flag
, item_number
, transaction_type
, sales_account
, set_process_id
)
VALUES ( ln_item_id
, ln_whse_id
, SYSDATE
, ln_user_id
, SYSDATE
, ln_user_id
, ln_login_id
, lc_item_no
, 1
, lc_item_no
, 'UPDATE'
, ln_ccid
, ln_process_id
);

END IF;

EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Data Not inserted into tables -'||ln_item_id);
RAISE_APPLICATION_ERROR(-20057,'Error While Insert into Stg Table '||SQLERRM);
END;
END IF;
-----------------------------------------------------------------
-- END IF;
END LOOP;
COMMIT;
Fnd_File.put_line(apps.fnd_file.LOG,'File Uploaded into interface table succussfully');
Fnd_File.put_line(apps.fnd_file.LOG,'Process id for this upload is - '||ln_process_id);

UTL_FILE.FCLOSE_ALL;
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Operation: '||' File Not Exit in OTM budget Directory ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20051,'File Not Exit in MTL Inv Directory');
WHEN UTL_FILE.INVALID_PATH THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Path');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20052,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Mode');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20053,'Invalid Mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
Fnd_File.put_line(Fnd_File.output,'Invalid File Handle ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20054,'Invalid File Handle');
WHEN UTL_FILE.WRITE_ERROR THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Write Error ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20055,'Invalid Write Error');
WHEN UTL_FILE.READ_ERROR THEN
Fnd_File.put_line(Fnd_File.output,'Read Error ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20056,'Read Error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20057,'Check the File Data Format '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
END mtl_inv_acct_upload;

Item Classification program for New Items

CREATE OR REPLACE procedure APPS.XXXXXX_INV_ITEM_CLASSIFI_P (ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER, ln_temp_id IN NUMBER,ln_organization_id IN NUMBER, ln_inventory_item_id IN NUMBER) IS
CURSOR lcur_rgn (ln_organization_id NUMBER) IS
SELECT templ_org_regns_id
FROM jai_templ_org_regns
WHERE template_id = ln_temp_id
AND organization_id IN (
SELECT inventory_organization_id
FROM hr_locations
WHERE inactive_date IS NULL
AND inventory_organization_id IS NOT NULL
AND inventory_organization_id =
NVL (ln_organization_id, inventory_organization_id)
GROUP BY inventory_organization_id)
ORDER BY 1;
ln_org_regns NUMBER;
Begin
For i in lcur_rgn(ln_organization_id) loop
Exit when lcur_rgn%notfound;
Begin
SELECT templ_org_regns_id
INTO ln_org_regns
FROM jai_templ_itm_regns
WHERE templ_org_regns_id = i.templ_org_regns_id
AND inventory_item_id = ln_inventory_item_id;
Exception
When Others then
ln_org_regns:=null;
End;
If ln_org_regns is null THEN
INSERT INTO jai_templ_itm_regns
(templ_itm_regns_id, templ_org_regns_id,
inventory_item_id, creation_date, created_by,
last_update_date, last_update_login, last_updated_by
)
VALUES (jai_templ_itm_regns_s.NEXTVAL, i.templ_org_regns_id,
ln_inventory_item_id, SYSDATE, fnd_profile.VALUE ('USER_ID'),
SYSDATE, fnd_profile.value('LOGIN_ID'), fnd_profile.VALUE ('USER_ID')
);
End if;
End loop;
Commit;
End;

Inventory Item VAT Assigment Program

CREATE OR REPLACE PROCEDURE APPS.XXATFL_INV_ITEM_VAT_CONV_P(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER,P_TEMPLATE_id NUMBER) IS

Cursor lcur_valid_org IS

Select INVENTORY_ORGANIZATION_ID org_id from HR_LOCATIONS_V hlv
Where hlv.INACTIVE_DATE is null
and hlv.INVENTORY_ORGANIZATION_ID is not null
and not exists (Select 'x' from JAI_TEMPL_ORG_REGNS jtor
where jtor.organization_id=hlv.inventory_organization_id
and jtor.template_id=p_template_id);

Cursor lcur_valid_items (ln_org_id NUMBER, ln_temp_id NUMBER) IS
Select jtor.templ_org_regns_id,jtor.template_id,misb.inventory_item_id
from jai_templ_org_regns jtor, mtl_system_items_b misb, hr_locations_v hlv
where jtor.organization_id=misb.organization_id
and jtor.organization_id = hlv.inventory_organization_id
and hlv.inactive_date is null
and hlv.inventory_organization_id is not null
and hlv.inventory_organization_id=ln_org_id
and jtor.template_id=ln_temp_id
and misb.segment1 in (select item_no from ic_item_mst where INACTIVE_IND=0);
--order by 1,2,3,4

ln_user NUMBER:=fnd_profile.value('USER_ID');
Begin
For i in lcur_valid_org loop
Exit when lcur_valid_org%notfound;
Begin
INSERT INTO JAI_TEMPL_ORG_REGNS
(
TEMPL_ORG_REGNS_ID,
TEMPLATE_ID ,
ORGANIZATION_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATED_BY
)
VALUES
(
JAI_TEMPL_ORG_REGNS_S.nextval,
p_template_id,
i.org_id,
sysdate,
ln_user,
sysdate,
APPS.FND_GLOBAL.LOGIN_ID,
ln_user
);
Commit;
Exception
When Others then
fnd_file.put_line( fnd_file.output,' Error While Inserting into JAI_TEMPL_ORG_REGNS table '||'Template Id :'||p_template_id||'Inv Org Id'||i.org_id ||SQLERRM );
End;
FOR j in lcur_valid_items (i.org_id,p_template_id) loop
Begin
INSERT INTO JAI_TEMPL_ITM_REGNS
(
TEMPL_ITM_REGNS_ID,
TEMPL_ORG_REGNS_ID,
INVENTORY_ITEM_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY
)
VALUES
(jai_templ_itm_regns_s.nextval,
j.templ_org_regns_id,
j.inventory_item_id,
sysdate,
ln_user,
sysdate,
APPS.FND_GLOBAL.LOGIN_ID,
ln_user);
Commit;
Exception
When Others then
fnd_file.put_line( fnd_file.output,' Error While Inserting into JAI_TEMPL_ITM_REGNS table : '||'Templ Org Regns Id: '||j.templ_org_regns_id||'Item Id: '||j.inventory_item_id|| SQLERRM );
End;
End loop;
End loop;
--- Deleting the records for start with 6 series items as per requirement of User
DELETE FROM JAI_TEMPL_ITM_REGNS jtir
where
exists
(select msib.inventory_item_id from mtl_system_items_b msib,ic_item_mst iim
where msib.organization_id=23
and Iim.item_no=msib.segment1
AND msib.inventory_item_id=jtir.inventory_item_id
and msib.segment1 like '6%'
and iim.INACTIVE_IND=0);
commit;
-----

End;

OA Inventory Open API

Spec
---

PROCEDURE inventory_open_api ( Errbuf OUT VARCHAR2
,Retcode OUT NUMBER
, p_company IN VARCHAR2
, p_fiscal_year IN NUMBER
, p_period_nm IN VARCHAR2)

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

PROCEDURE inventory_open_api ( Errbuf OUT VARCHAR2
,Retcode OUT NUMBER
, p_company IN VARCHAR2
, p_fiscal_year IN NUMBER
, p_period_nm IN VARCHAR2)
AS
CURSOR ls_period_open( lc_company VARCHAR2
, ln_fiscal_year NUMBER
, lc_period_nm VARCHAR2) is
SELECT a.organization_id,b.ORGANIZATION_CODE,b.ORGANIZATION_NAME,a.created_by,a.last_update_login,a.period_close_date,a.open_flag
FROM org_acct_periods a,org_organization_definitions b
WHERE a.period_set_name = lc_company
AND a.period_year = (ln_fiscal_year)
AND a.organization_id not in (select organization_id from org_acct_periods where period_name = p_period_nm)
-- AND a.period_name = lc_period_nm
AND to_char(a.period_name) = to_char((ADD_MONTHS(sysdate,-1)),'MON-YY')
-- AND a.organization_id = 44
-- AND a.open_flag = 'Y'
AND a.organization_id = b.organization_id;
i ls_period_open%rowtype;
CURSOR ls_slct_for_data (lc_prd_nm VARCHAR2
,ln_org_id NUMBER
,ln_prd_yr NUMBER) is
SELECT
GLP.period_set_name,
GLP.period_year,
GLP.period_num,
GLP.period_name,
GLP.description,
GLP.end_date,
'Y',
-1
FROM gl_periods GLP
WHERE GLP.period_set_name = 'AGROCAL'
AND GLP.period_name = lc_prd_nm
AND GLP.period_type = '1'
AND GLP.adjustment_period_flag = 'N'
AND (GLP.period_name, GLP.period_year)
NOT IN
(SELECT period_name, period_year
FROM org_acct_periods
WHERE organization_id = ln_org_id)
AND NOT EXISTS
(SELECT period_start_date
FROM org_acct_periods
WHERE organization_id = ln_org_id
AND period_year = ln_prd_yr
AND period_name = lc_prd_nm);
j ls_slct_for_data%rowtype;
lc_return_status VARCHAR2(1);
lc_msg_data VARCHAR2(2000);
lc_errbuf VARCHAR2(2000);
ln_msg_count NUMBER;
p_closing_rowid VARCHAR2(100);
x_wip_failed BOOLEAN;
x_close_failed BOOLEAN;
x_download_failed BOOLEAN;
x_req_id NUMBER;
x_return_status VARCHAR2(100);
x_prior_period_open BOOLEAN;
x_new_acct_period_id NUMBER;
x_duplicate_open_period BOOLEAN;
x_commit_complete BOOLEAN;
x_last_scheduled_close_date DATE;
ln_user_id NUMBER;
ln_org_id NUMBER;
ln_resp_id NUMBER;
ln_resp_app_id NUMBER;
lc_curr_per VARCHAR2(30);
ln_fsc_yr NUMBER;
ln_prd_nm NUMBER;
lc_last_day DATE;
Begin
-- fnd_file.put_line(fnd_file.log,'1st parameter - '||p_company||' - 2nd Parameter - '||p_fiscal_year||' - 3rd Parameter - '||p_period_num);
fnd_file.put_line(fnd_file.output,'WHSE'||' '||'PERIOD NUM'||' '||'PERIOD YR');
ln_user_id := 1738; --:=APPS.FND_PROFILE.VALUE('USER_ID');
ln_resp_id := APPS.FND_PROFILE.VALUE('RESP_ID');
ln_resp_app_id := APPS.FND_PROFILE.VALUE('RESP_APPL_ID');
ln_org_id := APPS.FND_PROFILE.VALUE('ORG_ID');
BEGIN
FND_client_info.set_org_context(ln_org_id);
END;
BEGIN
FND_GLOBAL.apps_initialize(1738,ln_resp_id,ln_resp_app_id);
END;
For i in ls_period_open(p_company, p_fiscal_year,p_period_nm) loop
Exit when ls_period_open%notfound;
For j in ls_slct_for_data(p_period_nm,i.organization_id,p_fiscal_year) loop
Exit when ls_slct_for_data%notfound;
Select last_day(to_char('01-'||j.period_name)) into lc_last_day
From dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'period_set_name is -'||j.period_year||'-'||i.organization_id||'-'||j.period_name||'-'||lc_last_day);
CST_ACCOUNTINGPERIOD_PUB.Open_Period(
p_api_version => 1.0,
p_org_id => i.organization_id,
p_user_id => i.created_by,
p_login_id => i.LAST_UPDATE_LOGIN,
p_acct_period_type => '1',
p_org_period_set_name => 'AGROCAL',
p_open_period_name => j.period_name,--ln_period_close.period_name,
p_open_period_year => j.period_year,--p_fiscal_year,
p_open_period_num => j.period_num,--p_period_num,
x_last_scheduled_close_date => x_last_scheduled_close_date,
p_period_end_date => lc_last_day,-- '30-NOV-2010',--to_char(last_day(sysdate),'DD-MON-RRRR'),
x_prior_period_open => x_prior_period_open,
x_new_acct_period_id => x_new_acct_period_id,
x_duplicate_open_period => x_duplicate_open_period,
x_commit_complete => x_commit_complete,
x_return_status => x_return_status);
FND_FILE.PUT_LINE(fnd_file.output,i.ORGANIZATION_CODE||' '||j.period_num||' '||j.period_year);
FND_FILE.PUT_LINE(FND_FILE.LOG,'STATUS : ' || lc_return_status);
IF lc_return_status <> 'S' THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'STATUS : ' || lc_return_status);
COMMIT;
IF ln_msg_count <= 1 THEN
lc_errbuf := lc_errbuf || lc_msg_data;
ELSE
FOR ln_i IN 1 .. ln_msg_count LOOP
lc_errbuf := lc_errbuf ||' '|| FND_MSG_PUB.GET ( ln_i, 'F' );
FND_FILE.PUT_LINE(FND_FILE.LOG,'STATUS : ' || lc_errbuf);
END LOOP;
END IF;
END IF;
IF i.period_close_date is not null and i.open_flag = 'Y' Then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory period cannot open for - '||i.organization_code|| 'as there is problem.');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory period opened for organization - '||i.organization_code);
End If;
End loop;
END LOOP;
Commit;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception - Inventory Close API'||sqlerrm);
End;
---

OA Inventory Close API

Spec---------

PROCEDURE inventory_close_api ( Errbuf OUT VARCHAR2
,Retcode OUT NUMBER
, p_company IN VARCHAR2
, p_fiscal_year IN NUMBER
, p_period_num IN NUMBER)l

Body
---------------------

PROCEDURE inventory_close_api ( Errbuf OUT VARCHAR2
,Retcode OUT NUMBER
, p_company IN VARCHAR2
, p_fiscal_year IN NUMBER
, p_period_num IN NUMBER)
IS

CURSOR ls_period_close( lc_company VARCHAR2
, ln_fiscal_year NUMBER
, ln_period_num NUMBER) is

SELECT b.ORGANIZATION_CODE,b.ORGANIZATION_NAME,a.* from org_acct_periods a,org_organization_definitions b
WHERE a.period_set_name = lc_company
AND a.period_year = (ln_fiscal_year)
AND a.period_num = ln_period_num
AND a.open_flag = 'Y'
AND a.organization_id = b.organization_id;

lc_return_status VARCHAR2(1);
lc_msg_data VARCHAR2(2000);
lc_errbuf VARCHAR2(2000);
ln_msg_count NUMBER;
p_closing_rowid VARCHAR2(100);
x_wip_failed BOOLEAN;
x_close_failed BOOLEAN;
x_download_failed BOOLEAN;
x_req_id NUMBER;
x_return_status VARCHAR2(100);
ln_user_id NUMBER;
ln_org_id NUMBER;
ln_resp_id NUMBER;
ln_resp_app_id NUMBER;

Begin
fnd_file.put_line(fnd_file.log,'1st paramaeter - '||p_company||' - 2nd Parameter - '||p_fiscal_year||' - 3rd Parameter - '||p_period_num);
fnd_file.put_line(fnd_file.output,'WHSE'||' '||'PERIOD NUM'||' '||'PERIOD YR');

ln_user_id := 1738; --:=APPS.FND_PROFILE.VALUE('USER_ID');
ln_resp_id := APPS.FND_PROFILE.VALUE('RESP_ID');
ln_resp_app_id := APPS.FND_PROFILE.VALUE('RESP_APPL_ID');
ln_org_id := APPS.FND_PROFILE.VALUE('ORG_ID');

BEGIN
FND_client_info.set_org_context(ln_org_id);
END;
BEGIN
FND_GLOBAL.apps_initialize(1738,ln_resp_id,ln_resp_app_id);
END;
For ln_period_close in ls_period_close(p_company, p_fiscal_year, p_period_num) loop
Exit when ls_period_close%notfound;
CST_ACCOUNTINGPERIOD_PUB.Close_Period(
p_api_version => 1.0,
p_org_id => ln_period_close.ORGANIZATION_ID,
p_user_id => ln_period_close.created_by,
p_login_id => ln_period_close.LAST_UPDATE_LOGIN,
p_closing_acct_period_id => ln_period_close.ACCT_PERIOD_ID ,
p_period_close_date => Sysdate,
p_schedule_close_date => ln_period_close.SCHEDULE_CLOSE_DATE,
p_closing_rowid => p_closing_rowid,
x_wip_failed => x_wip_failed,
x_close_failed => x_close_failed,
x_download_failed => x_download_failed,
x_req_id => x_req_id,
x_return_status => x_return_status);
FND_FILE.PUT_LINE(fnd_file.output,ln_period_close.ORGANIZATION_CODE||' '||ln_period_close.period_num||' '||ln_period_close.period_year);
IF lc_return_status <> 'S' THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'STATUS : ' || lc_return_status);
COMMIT;
IF ln_msg_count <= 1 THEN
lc_errbuf := lc_errbuf || lc_msg_data;
ELSE
FOR ln_i IN 1 .. ln_msg_count LOOP
lc_errbuf := lc_errbuf ||' '|| FND_MSG_PUB.GET ( ln_i, 'F' );
FND_FILE.PUT_LINE(FND_FILE.LOG,'STATUS : ' || lc_errbuf);
END LOOP;
END IF;
END IF;
IF ln_period_close.period_close_date is not null and ln_period_close.open_flag = 'Y' Then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory period still open for - '||ln_period_close.organization_code|| 'as there are pending transactions for the month.');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory period closed for organization - '||ln_period_close.organization_code);
End If;
End loop;
Commit;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception - Inventory Close API'||sqlerrm);
End;

Purchase Order cancellation program(API)

Spec--

PROCEDURE XXXXXX_PO_STANDARD_CANCELLATION (errbuf out varchar2,retcode out varchar2,From_po_num varchar2,To_po_num varchar2);
--------------------------------------------------------------------------------------------
Body--

PROCEDURE XXXXXX_PO_STANDARD_CANCELLATION (errbuf out varchar2,retcode out varchar2,From_po_num varchar2,To_po_num varchar2)
IS
x_result VARCHAR2(50);
cursor c_po_closed is
SELECT ph.type_lookup_code, ph.segment1 po_num, ph.po_header_id,
pll.quantity, pll.quantity_billed, pll.quantity_received,
ph.start_date, ph.end_date, pll.po_line_id, pll.line_location_id
FROM po_headers_all ph, po_line_locations_all pll --
WHERE ph.po_header_id = pll.po_header_id
AND ph.type_lookup_code = 'STANDARD'
AND ph.enabled_flag = 'Y'
AND ph.authorization_status = 'APPROVED'
AND TRUNC (SYSDATE) - (ph.creation_date) > 90
AND pll.quantity_received = pll.quantity_billed
AND pll.quantity_cancelled = 0
AND pll.quantity <> pll.quantity_billed
AND pll.quantity <> pll.quantity_received
AND pll.closed_code = 'OPEN'
AND ph.po_header_id NOT IN (SELECT po_header_id
FROM po_distributions
WHERE project_id IS NOT NULL)
UNION
SELECT ph.type_lookup_code, ph.segment1 po_num, ph.po_header_id,
pll.quantity, pll.quantity_billed, pll.quantity_received,
ph.start_date, ph.end_date, pll.po_line_id, pll.line_location_id
FROM po_headers_all ph, po_line_locations_all pll --
WHERE ph.po_header_id = pll.po_header_id
AND ph.type_lookup_code = 'STANDARD'
AND ph.enabled_flag = 'Y'
AND ph.authorization_status = 'APPROVED'
AND TRUNC (SYSDATE) - (ph.creation_date) > 90
AND pll.quantity_received = pll.quantity_billed
AND pll.quantity_cancelled = 0
AND pll.quantity <> pll.quantity_billed
AND pll.quantity <> pll.quantity_received
AND pll.closed_code = 'OPEN'
AND ph.po_header_id NOT IN (SELECT po_header_id
FROM po_distributions
WHERE project_id IS NOT NULL);
lc_cancel VARCHAR2(25);
ln_user_id number;
ln_resp_id number;
ln_resp_app_id number;
BEGIN
FND_FILE.PUT_LINE(fnd_file.log,fnd_global.user_id||','||From_po_num||','||From_po_num);
ln_user_id := APPS.FND_PROFILE.VALUE('USER_ID');
IF ln_user_id IS NULL THEN
ln_user_id := -1;
END IF;
ln_resp_id := APPS.FND_PROFILE.VALUE('RESP_ID');
ln_resp_app_id := APPS.FND_PROFILE.VALUE('RESP_APPL_ID');
Begin
FND_GLOBAL.apps_initialize(ln_user_id,ln_resp_id,ln_resp_app_id);
end;
FND_FILE.PUT_LINE(fnd_file.log,ln_user_id||','||ln_resp_id||','||ln_resp_app_id);
FND_FILE.PUT_LINE(fnd_file.output,'========================================================================================================');
FND_FILE.PUT_LINE(fnd_file.output,'PO_NUMBER'||' |'||'PO_HEADER_ID'||' |'||'PO_LINE_ID'||' |'||'Qty'||' |'||'quantity_billed'||' |'||'quantity_received');
FND_FILE.PUT_LINE(fnd_file.output,'========================================================================================================');
For d_po_closed in c_po_closed loop
FND_FILE.PUT_LINE(fnd_file.log,d_po_closed.po_header_id||' | '
||d_po_closed.po_line_id||' | '||d_po_closed.po_num||','||lc_cancel);
po_document_control_pub.control_document(1.0, -- api version
FND_API.g_true, -- init msg
FND_API.g_true, -- commit
x_result, -- result
'PO', --doc type
d_po_closed.type_lookup_code, --'STANDARD', --sub doc type
d_po_closed.po_header_id, -- header_id
d_po_closed.po_num, -- po_number
NULL, -- release_id
NULL, -- release num
d_po_closed.po_line_id, -- po line id
NULL, -- line_num
d_po_closed.line_location_id, -- line_locaiton id
NULL, -- doc ship num
'CANCEL', -- action
SYSDATE, -- action date
'ADMN REASON', -- cancel reason
'N', -- cancel requisitions
'N', -- print flag
'100% Advance Payment', -- note to vendor
'N');
FND_FILE.PUT_LINE(fnd_file.output,Rpad(d_po_closed.po_num,12,' ')||'|'||Rpad(d_po_closed.po_header_id,18,' ')||'|'||Rpad(d_po_closed.po_line_id,11,' ')||' |'
||Rpad(d_po_closed.Quantity,10,' ')||'|'||Rpad(d_po_closed.quantity_billed,14,' ')||' |'||d_po_closed.quantity_received);
End loop;
End;

Requisition Cancel Program(API)

Spec
Procedure XXXXXX_PO_REQ_CANCEL_PRG(errbuf out varchar2,retcode out varchar2,Req_num Number);
--------------------------------------------------------------------------
Body

Procedure XXXXXX_PO_REQ_CANCEL_PRG(errbuf out varchar2,retcode out varchar2,Req_num Number)
is

l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
lv_header_id po_tbl_number;
lv_line_id po_tbl_number;
m NUMBER := NULL;
l_msg_dummy VARCHAR2 (2000);
l_output VARCHAR2 (2000);

Cursor lcur_req_can is
Select poh.requisition_header_id
,pol.requisition_line_id
,poh.authorization_status
,poh.Segment1 Po_Req_Num
,pol.item_description
,pol.line_num
,quantity
From po_requisition_headers_all poh
,po_requisition_lines_all pol
Where poh.authorization_status = 'APPROVED'
And poh.requisition_header_id = pol.requisition_header_id
And pol.line_location_id is null
And pol.source_type_code = 'VENDOR'
And nvl(pol.cancel_flag,'N') = 'N'
And poh.segment1 = nvl(Req_num,poh.segment1);

BEGIN
m := 1;
FOR i IN lcur_req_can LOOP
lv_header_id := po_tbl_number (i.requisition_header_id);
lv_line_id := po_tbl_number (i.requisition_line_id);
po_req_document_cancel_grp.cancel_requisition
(p_api_version => 1.0,
p_req_header_id => lv_header_id,
p_req_line_id => lv_line_id,
p_cancel_date => SYSDATE,
p_cancel_reason => 'Cancelled Requisition',
p_source => 'REQUISITION',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END LOOP;
COMMIT;
FND_FILE.PUT_LINE(fnd_file.log,'Return Status is - '||l_return_status);
IF l_return_status <> 'S'
THEN
fnd_msg_pub.get (m, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (m) || ': ' || l_msg_data);
FND_FILE.PUT_LINE(fnd_file.log,'Out put is - '||l_output);
END IF;
END;

Sales Order Header Close API

------------Spec
PROCEDURE xxxxxx_order_hdr_close_prg (errbuf out varchar2,retcode out varchar2,p_ord_num number,p_whse_code varchar2);
---------------------------------------
------------Body

PROCEDURE xxxxxx_order_hdr_close_prg (errbuf out varchar2,retcode out varchar2,p_ord_num number,p_whse_code varchar2) is

--Declare

l_result VARCHAR2(60);
l_file_val VARCHAR2(60);
l_result_out varchar2(30);
l_return_status varchar2(30);
err_msg VARCHAR2(240);
ln_cnt NUMBER;

Cursor lc_status_check is

Select count(ool.line_id) cur_cnt,ooh.header_id
From oe_order_headers_All ooh,oe_order_lines_all ool--,oe_transaction_types_v ott
Where 1=1
And ooh.flow_status_code in ('BOOKED','ENTERED')
And ooh.header_id = ool.header_id
Group By ooh.header_id;

Cursor C1 is

SELECT l.line_id
FROM oe_order_lines_all l, wf_items
WHERE (l.line_id) = to_number(item_key)
AND item_type = 'OEOL'
AND l.open_flag = 'N'
AND end_date IS NULL
AND l.flow_status_code = 'CANCELLED';

ln_user_id number;
ln_resp_id number;
ln_resp_app_id number;

Begin

ln_user_id := APPS.FND_PROFILE.VALUE('USER_ID');
IF ln_user_id IS NULL THEN
ln_user_id := -1;
END IF;
ln_resp_id := APPS.FND_PROFILE.VALUE('RESP_ID');
ln_resp_app_id := APPS.FND_PROFILE.VALUE('RESP_APPL_ID');
Begin
FND_GLOBAL.apps_initialize(ln_user_id,ln_resp_id,ln_resp_app_id);
end;

For j in C1 Loop
Exit When C1%notfound;
oe_debug_pub.debug_on;
oe_debug_pub.initialize;
l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_Debug_pub.setdebuglevel(5);
fnd_file.put_line(fnd_file.output,'log file name is: '||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
fnd_file.put_line(fnd_file.output,'Line id is -'||j.line_id);
OE_Standard_WF.OEOL_SELECTOR
( p_itemtype => 'OEOL'
,p_itemkey => to_char(j.line_id)
,p_actid => 12345
,p_funcmode => 'SET_CTX'
,p_result => l_result
);
fnd_file.put_line(fnd_file.output,'Line id is -'||j.line_id);
fnd_file.put_line(fnd_file.output,' Return Status is for Line: '||l_result);

wf_engine.handleError('OEOL', to_char(j.line_id), 'CLOSE_LINE','RETRY','COMPLETE');

End Loop;
Commit;

For i in lc_status_check loop
Exit When lc_status_check%notfound;

ln_cnt:= 0;

BEGIN

Select Count(line_id) into ln_cnt
From oe_order_lines_all
Where header_id = i.header_id
And flow_status_code in ('CLOSED','CANCELLED');
Exception
WHEN OTHERS THEN
ln_cnt := 0;
END;

If i.cur_cnt = ln_cnt Then

oe_debug_pub.debug_on;
oe_debug_pub.initialize;
l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_Debug_pub.setdebuglevel(5);
fnd_file.put_line(fnd_file.log,'log file name is: '||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);

OE_Standard_WF.OEOH_SELECTOR
(p_itemtype => 'OEOH'
,p_itemkey => to_char(i.header_id)
,p_actid => 12345
,p_funcmode => 'SET_CTX'
,p_result => l_result
);
fnd_file.put_line(fnd_file.log,' Return Status is for Header: '||l_result);

wf_engine.handleError('OEOH', to_char(i.header_id), 'CLOSE_HEADER','RETRY','COMPLETE');

fnd_file.put_line(fnd_file.log,'Order Header Id : '|| i.header_id);

END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
err_msg := 'Error :'||SQLERRM;
fnd_file.put_line(fnd_file.log,'Error: Pls ROLLBACK as following error ocurred:- '||SQLERRM);
OE_DEBUG_PUB.ADD(err_msg);
END xxxxxx_order_hdr_close_prg;

FPO(Firm Planned Orders) Cancellation API

Specification

CREATE OR REPLACE PACKAGE APPS.XXXXX_GME_BATCH_CANCEL_PKG IS

PROCEDURE batch_cancel (errbuf out varchar2,retcode out varchar2, p_plant_Code VARCHAR2, p_batch_no VARCHAR2);

END XXXXX_GME_BATCH_CANCEL_PKG;
/

----Body

CREATE OR REPLACE PACKAGE BODY APPS.XXXXXX_GME_BATCH_CANCEL_PKG AS

PROCEDURE batch_cancel (errbuf out varchar2,retcode out varchar2, p_plant_Code VARCHAR2, p_batch_no VARCHAR2)
IS
p_api_version NUMBER := gme_api_pub.api_version;
p_validation_level NUMBER := gme_api_pub.max_errors;
p_init_msg_list BOOLEAN := FALSE ;
p_commit BOOLEAN := FALSE ;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_list VARCHAR2 (2000);
x_msg_data VARCHAR2 (2000);
x_msg_index NUMBER;
p_batch_header gme_batch_header%ROWTYPE;
x_batch_header gme_batch_header%ROWTYPE;
ln_user_id NUMBER;
ln_org_id NUMBER;
ln_resp_id NUMBER;
ln_resp_app_id NUMBER;

CURSOR lcur_can_batch (p_plant_code VARCHAR2, p_batch_no VARCHAR2) IS

SELECT batch_id, plant_code, batch_no, batch_type
FROM gme_batch_header
WHERE batch_type = 10
AND plant_code = nvl(p_plant_code,plant_code)
AND batch_no = nvl(p_batch_no,batch_no)
AND batch_status = 1;

BEGIN

--- Initialization values
ln_user_id := APPS.FND_PROFILE.VALUE('USER_ID');
ln_resp_id := APPS.FND_PROFILE.VALUE('RESP_ID');
ln_resp_app_id := APPS.FND_PROFILE.VALUE('RESP_APPL_ID');
ln_org_id := APPS.FND_PROFILE.VALUE('ORG_ID');

BEGIN
FND_client_info.set_org_context(ln_org_id);
END;

BEGIN
FND_GLOBAL.apps_initialize(ln_user_id,ln_resp_id,ln_resp_app_id);
END;

FOR i IN lcur_can_batch (p_plant_code,p_batch_no) LOOP
EXIT WHEN lcur_can_batch%NOTFOUND;

--- passing batch values for cancellation

p_batch_header.batch_id := i.batch_id;
p_batch_header.plant_code := i.plant_code;
p_batch_header.batch_no := i.batch_no;
p_batch_header.batch_type := i.batch_type;

gme_api_main.cancel_batch (
p_api_version => p_api_version,
p_validation_level => p_validation_level,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit ,
x_message_count => x_msg_count,
x_message_list => x_msg_list,
x_return_status => x_return_status,
p_batch_header => p_batch_header,
x_batch_header => x_batch_header
);

IF x_return_status <> 'S' THEN
gme_api_grp.count_and_get(
p_encoded => 'F',
x_data => x_msg_data,
x_count => x_msg_index);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Plant Code = ' ||i.plant_code||' '||' Batch No = '||i.batch_no||' '||'has been Errored');
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Plant Code = ' ||i.plant_code||' '||' Batch No = '||i.batch_no||' '||'has been cancelled');
END IF;

END LOOP;
COMMIT;
END;

END XXXXXX_GME_BATCH_CANCEL_PKG;
/

How to Backorder Shipment through API

PROCEDURE xxxx_iso_shipping_bkrd (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
--Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);
--Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
p_action_code VARCHAR2 (15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
/*Handle exceptions*/
fail_api EXCEPTION;

CURSOR lc_get_iso_ship_data
IS
SELECT ooh.header_id, ooh.order_number, wnd.delivery_id,
wnd.NAME delivery_name,
SUBSTR (wdd.source_header_type_name, 1, 3) whse
FROM oe_order_headers_all ooh,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda
WHERE 1 = 1
AND ooh.header_id = wdd.source_header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wdd.released_status = 'Y'
--AND ooh.order_source_id = 10
--And wda.delivery_id = '776213' --- if required pass delivery id as parameter in concurrent program
AND TRUNC (SYSDATE) - TO_DATE (ooh.booked_date) = 1;

ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_resp_app_id NUMBER;
ln_reason_id NUMBER;
BEGIN
ln_user_id := apps.fnd_profile.VALUE ('USER_ID');

IF ln_user_id IS NULL
THEN
ln_user_id := -1;
END IF;

ln_resp_id := apps.fnd_profile.VALUE ('RESP_ID');
ln_resp_app_id := apps.fnd_profile.VALUE ('RESP_APPL_ID');

BEGIN
fnd_global.apps_initialize (ln_user_id, ln_resp_id, ln_resp_app_id);
END;

FOR lc_iso_ship_dt IN lc_get_iso_ship_data
LOOP
EXIT WHEN lc_get_iso_ship_data%NOTFOUND;
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := lc_iso_ship_dt.delivery_id;
--620600; -- The delivery that needs to be confirmed
p_delivery_name := lc_iso_ship_dt.delivery_name;
--'620600'; -- The delivery name,
p_sc_action_flag := 'C'; -- Ship entered quantity.
p_sc_stage_del_flag := 'N'; --
--p_sc_trip_ship_method := Null; -- The ship method code
/*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
wsh_deliveries_pub.delivery_action
(p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_wv_override_flag => p_wv_override_flag,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name
);

IF (x_return_status <> wsh_util_core.g_ret_sts_success)
THEN
fnd_file.put_line (fnd_file.LOG,
'Successfully BackOrdered for Order Number - '
|| lc_iso_ship_dt.order_number
);
COMMIT;
ELSE
fnd_file.put_line
(fnd_file.LOG,
'Could not able to Back Order Line Due to Following Reasons -'
|| x_return_status
|| ' for header id - '
|| lc_iso_ship_dt.header_id
);
ROLLBACK;
/* FOR j IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
fnd_file.put_line(fnd_file.log,'Error Message is=> '|| l_msg_data);
END LOOP;*/
END IF;
END LOOP;

COMMIT;
END;