Oracle Apps

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;