Apply INDIA LOCALIZATION Patch

India Localization Patch Application Tool ( Discrete and OPM )



Required Patches:




6491231 ( REF : 6059025 - MULTINODE : PATCH TOOL ENHANCED TO HADLE, OA FRAMEWORK AND XMLP )

5498551 ( REF : 5219225 - CUMULATIVE PATCHSET - IN60107 )

6167056 ( ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS)


Installation(Installing patch tool):



1.Download patch 6059025.

2.mv 6059025.zip $JA_TOP/.

3.unzip 6059025.zip

4 Cd inpatch

5 run command :

perl install.pl appspwd=apps japwd=ja





INDIA LOCALIZATION CUMULATIVE PATCHSET IN60107


Download patch 5498551


Mv 5498551.zip $JA_TOP
Unzip 5498551.zip
Cd 5498551



1.check the pre-requisites:



find the patchset level using: (FND patchset C or above)


select patch_level, application_name from fnd_product_installations fpi, fnd_application_tl fat where patch_level is not null and fpi.application_id = fat.application_id and patch_level like '%FND%' ;

( FND – G )




2.Take a backup of database.



Download patch 5498551 ( REF : 5219225 - CUMULATIVE PATCHSET - IN60107 )


This localization patchset files: 70 MB disk space
Check df –h


Check the datafile has atleast 100M free space
First find related tablespace using
Cd erpp/erppappl/admin/scripts/ERPP1_erpp1/
Sh adstpall apps/apps



Run adadmin

Put in maintenance mode.


Check invalid objects

Sqlplus “/ as sysdba”

Exec UTL_RECOMP.RECOMP_PARALLEL(8);

(note the invalid objects).

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='JA'; 


Tablespace – ja

The datafile for ja tablespace should have 100MB free space.
select TABLESPACE_NAME from dba_indexes where owner='JA'; 

Tablespace – jx

The datafile for should have 50MB free space.
Rollback segment should have 30MB free space.
Get the undo tablespace name using:


Show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string APPS_UNDOTS1

Check the free space
SELECT Total.name "Tablespace Name", nvl(Free_space, 0) Free_space, nvl(total_space-Free_space, 0) Used_space, total_space FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space group by tablespace_name) Free, (select b.name, sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name) Total WHERE Free.Tablespace_name(+) = Total.name ORDER BY Total.name


ALTER DATABASE DATAFILE '/erpp/erppdata/jad01.dbf' RESIZE 100M;


Check if DBMS_SUPPORT package is present in your APPS schema by using the below query in APPS schema :


select object_name, object_type from all_objects where object_name = 'DBMS_SUPPORT' and object_type like 'PACKAGE%' ;
---------------------
DBMS_SUPPORT
PACKAGE

DBMS_SUPPORT
PACKAGE BODY


Run sqlplus apps/apps@ja_chk_stat.sql



Continue if only no issues in prechecks_5498551.log


Check 'JA_IN_INSTALL_CHECK_INFO_D does not exist in apps schema



select owner, table_name from all_tables where table_name = 'JA_IN_INSTALL_CHECK_INFO_D' and owner = 'APPS' ;



object does not exist.


Run….check for errors

select descriptive_flexfield_name, application_column_name, descriptive_flex_context_code from fnd_descr_flex_column_usages where descriptive_flexfield_name in ('AP_INVOICES') ;


Patching:



perl /erpp/erppappl/ja/11.5.0/inpatch/indpatch.pl drvr_file=5498551.drv fnd_patchset=G logfile=first.log test=y appspwd=apps japwd=ja mode=FCD systempwd=manager



perl /erpp/erppappl/ja/11.5.0/inpatch/indpatch.pl drvr_file=5498551.drv fnd_patchset=G logfile=org.log test=n appspwd=apps japwd=ja mode=FCD systempwd=manager



open another session and check log file


cd $JA_TOP/5498551
tail –f org.log
And also check the patching status running


select * from jai_applied_patches where patch_number = '5498551’;



Error:
Cannot copy---------to /erpp/erppappl/ar/11.5.0/JAINBOER.rdf permission denied
Cannot copy---------to /erpp/erppappl/ar/11.5.0/JAINARE1.rdf permission denied



chmod 775 /erpp/erppappl/ar/11.5.0/reports/US

cp /erpp/erppappl/ja/5498551/reports/US/ JAINBOER.rdf /erpp/erppappl/ar/11.5.0/JAINBOER.rdf

cp /erpp/erppappl/ja/5498551/reports/US/ JAINBOER.rdf /erpp/erppappl/ar/11.5.0/JAINARE1.rdf



Rerun

perl /erpp/erppappl/ja/11.5.0/inpatch/indpatch.pl drvr_file=5498551.drv fnd_patchset=G logfile=org.log test=n appspwd=apps japwd=ja mode=FCD systempwd=manager



And check log file for errors.




Run adadmin

Disable maintenance mode.


Go to

Cd erpp/erppappl/admin/scripts/ERPP1_erpp1/

Sh adstrtal.sh apps/apps


Login through front page and select Application



DeveloperFlexfielddescriptiveSegments



Press f11 and search “Oracle Payables” in Application and press ctrl+f11 and Title should be “Invoices”



Then uncheck the Freeze flexfield definition. Press “ok”



Click codeGlobal data elements then clickSegments


Just click last column

Note down the information of the segments attached to Attributes 1-5,14 and 15 and delete them.Then re-create them with the same information but with free attributes not in (1-5, 14, 15). And rename the window promt name to previous values.



Save and make “tick” in Freeze flexfield Definition. Then press “compile” and click “ok”.




POST-PATCH APPLICATION STEPS


(Migration should be done with the help of FUNCTIONAL user)


Run this script


sqlplus apps/apps @jai_rcv_chk_pending_delivery.sql



1.View the old Threshold Data:


SELECT DISTINCT a.vendor_id, a.vendor_site_id, b.vendor_name,
DECODE(a.vendor_site_id, 0 , 'Null Site', c.vendor_site_code) "Vendor Site Code", a.tds_section, a.tds_amount, decode(a.invoice_tds_flag, 'P', 'Per Invoice', 'A', 'All Invoices') FROM ja.ja_in_vendor_tds_info_dtl a, po_vendors b, po_vendor_sites_all c
WHERE a.vendor_id = b.vendor_id AND b.vendor_id = c.vendor_id
AND (a.vendor_site_id = c.vendor_site_id OR a.vendor_site_id = 0)


Cd /erpp/erppl/ja/5498551/misc/

sqlplus apps/apps@jai_ap_3841637_migrate_pkg_t.sql


2.PAN Number validation:


SELECT DISTINCT a.vendor_id, a.vendor_site_id, b.vendor_name,
DECODE(a.vendor_site_id, 0 , 'Null Site', c.vendor_site_code) "Vendor Site Code", a.pan_no, jai_ap_3841637_migrate_pkg_t.check_valid(a.pan_no, 10) PAN_NO_STATUS,
DECODE(a.vendor_site_id, 0, a.confirm_pan_flag, NULL) PAN_CONFIRM_FLAG
FROM ja_in_vendor_tds_info_hdr a, po_vendors b, po_vendor_sites_all c WHERE a.vendor_id = b.vendor_id
AND b.vendor_id = c.vendor_id AND (a.vendor_site_id = c.vendor_site_id OR a.vendor_site_id = 0)


4.Execute in apps schema:


DROP PACKAGE JAI_AP_3841637_MIGRATE_PKG_T ;


Do steps 5,6 only if any discrepancies in PAN No and PAN Confirm Flag as a result of output of step 3


5. UPDATE ja_in_vendor_tds_info_hdr
SET pan_no = '&New_PAN_NO'
WHERE vendor_id = &vendor_id
AND vendor_site_id = NVL(&vendor_Site_ID, vendor_site_id);



6. UPDATE ja_in_vendor_tds_info_hdr
SET confirm_pan_flag = '&Confirm_PAN_FLAG_Y_N'
WHERE vendor_id = NVL(&vendor_id_NULL_for_all_vendors, vendor_id);




1.Migration script for generating ETDS flat file:



Take "TDS 2.244% Sec. 194(C)" tax and assume that the total tds rate given is : 2.244% which includes 10% surcharge and 2% cess. And if the user had already entered the value: 2.244 in the field 'Percentage' and no value in the field 'Surcharge %age',Then he should query that particular tax code and give the value 0.244 in the
'Surcharge %age' field.


2.Run scripts present in the "misc" folder:



a. sqlplus apps/ @jai_ap_etds_4353842_mig1_apps.sql



b. SELECT tax_id , tax_name , tax_Rate , surcharge_rate
FROM JA_IN_TAX_CODE_APP;
Based on the above data, you might want to delete some taxes (as discussed with functional user) which do not contain cess in the tax rate.

DELETE JA_IN_TAX_CODE_APP
WHERE tax_id = &relevant_tax_id_from_query_above



c. sqlplus apps/ @jai_ap_etds_4353842_mig2_apps.sql

RG1 - Source Migration for Shipment / Manual AR Invoice Records :



Sqlplus apps/apps@ jai_mig_4346220.sql


output has 40>0
output iv has some value >

select * from ja_in_localization_errors where application_id = '4346220'


Select * from ja_in_rg_i where register_id = < register_id from above>
shipment transaction,


UPDATE ja_in_rg_i SET cess_amount = ( SELECT sum(func_tax_amount) FROM
ja_in_so_picking_lines jspl , ja_in_so_picking_tax_lines jsptl ,
ja_in_tax_codes jtc WHERE jspl.delivery_detail_id = jsptl.delivery_detail_id AND jspl.delivery_detail_id = <&reference_no from RG1 balance screen> AND jspl.organization_id = &organization_id AND jspl.location_id= &location_id AND jspl.inventory_item_id = &inventory_item_id AND jspl.quantity = &rg1_qty AND jtc.tax_id = jsptl.tax_id AND upper(jtc.tax_type) IN ('EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS')), source = 'WSH',
last_update_date = sysdate, last_updated_by = -4346220
WHERE register_id = ®ister_id;

commit;




AR Receivable transaction:


UPDATE ja_in_rg_i SET cess_amount = ( SELECT sum(func_tax_amount)
FROM ja_in_ra_customer_trx jrct, ja_in_ra_customer_trx_lines jrctl,
ja_in_ra_cust_trx_tax_lines jrcttl , ja_in_tax_codes jtc WHERE
jrct.customer_trx_id = jrctl.customer_trx_id AND jrct.customer_trx_id = <&reference_no from RG1 balance screen>
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrct.organization_id = &organization_id AND jrct.location_id = &location_id AND jrctl.inventory_item_id = inventory_item_id AND jtc.tax_id = jrcttl.tax_id AND upper(jtc.tax_type) IN ('EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS')
source = 'AR', last_update_date = sysdate ,
last_updated_by = -4346220 WHERE register_id = ®ister_id;

commit;




perl compile_il_apps_obj.pl appspwd=apps



Go to "Application Developer" Responsibility
- Open View -> Requests
- Click on "Submit New Request"
- Click on "Single Request"
- Enter "Generate Messages" in Name field
- Enter the following parameters
Language : US
Application : Asia/Pacific Localizations
Mode : DB_TO_RUNTIME
- Click Ok and then Submit the request, ensure that request completes successfully
Logout and Relogin



Conn po/po


create index ja_in_rcv_shipment_headers_n1 on
rcv_shipment_headers(receipt_num);


create index ja_in_rcv_transactions_n1 on rcv_transactions (rma_reference);


Conn ar/ar

create index ja_in_ra_customer_trx_all_n1 on
ra_customer_trx_all(interface_header_attribute1, interface_header_attribute6);


conn inv/inv

create index ja_in_mtl_transactions_n1 on
mtl_material_transactions(rcv_transaction_id);





CUSTOM.pll on $AU_TOP/resource.


Cp /erpp/erppappl/ja/5498551/patch/backup/resource/CUSTOM.pll /erpp/erppappl/au/11.5.0/resource/CUSTOM.pll


f60gen module=CUSTOM userid=apps/apps module_type=LIBRARY


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


6167056 ( ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS)

1.Download patch 6167056

2.mv 6167056.zip $JA_TOP

3.unzip 6167056.zip

4.cd 6167056
5.

adadminchange maintenance mode  enable maintenance mode.

perl /erpp/erppappl/ja/11.5.0/inpatch/indpatch.pl drvr_file= 6167056.drv fnd_patchset=G logfile=first.log test=y appspwd=apps japwd=ja mode=FCD systempwd=manager


perl /erpp/erppappl/ja/11.5.0/inpatch/indpatch.pl drvr_file=6167056.drv fnd_patchset=G logfile=org.log test=n appspwd=apps japwd=ja mode=FCD systempwd=manager


tail –f org.log


check logfile for any issues.


And also check patching status

select * from jai_applied_patches where patch_number = '6167056’;


1.View the old Threshold Data:


SELECT DISTINCT a.vendor_id, a.vendor_site_id, b.vendor_name,
DECODE(a.vendor_site_id, 0 , 'Null Site', c.vendor_site_code) "Vendor Site Code", a.tds_section, a.tds_amount, decode(a.invoice_tds_flag, 'P', 'Per Invoice', 'A', 'All Invoices') FROM ja.ja_in_vendor_tds_info_dtl a, po_vendors b, po_vendor_sites_all c
WHERE a.vendor_id = b.vendor_id AND b.vendor_id = c.vendor_id
AND (a.vendor_site_id = c.vendor_site_id OR a.vendor_site_id = 0)



Cd /erpp/erppl/ja/5498551/misc/

sqlplus apps/apps@jai_ap_3841637_migrate_pkg_t.sql


2.PAN Number validation:


SELECT DISTINCT a.vendor_id, a.vendor_site_id, b.vendor_name,
DECODE(a.vendor_site_id, 0 , 'Null Site', c.vendor_site_code) "Vendor Site Code", a.pan_no, jai_ap_3841637_migrate_pkg_t.check_valid(a.pan_no, 10) PAN_NO_STATUS,
DECODE(a.vendor_site_id, 0, a.confirm_pan_flag, NULL) PAN_CONFIRM_FLAG
FROM ja_in_vendor_tds_info_hdr a, po_vendors b, po_vendor_sites_all c WHERE a.vendor_id = b.vendor_id
AND b.vendor_id = c.vendor_id AND (a.vendor_site_id = c.vendor_site_id OR a.vendor_site_id = 0)


4.Execute in apps schema:


DROP PACKAGE JAI_AP_3841637_MIGRATE_PKG_T ;


Do steps 5,6 only if any discrepancies in PAN No and PAN Confirm Flag as a result of output of step 3


5. UPDATE ja_in_vendor_tds_info_hdr
SET pan_no = '&New_PAN_NO'
WHERE vendor_id = &vendor_id
AND vendor_site_id = NVL(&vendor_Site_ID, vendor_site_id);


6. UPDATE ja_in_vendor_tds_info_hdr
SET confirm_pan_flag = '&Confirm_PAN_FLAG_Y_N'
WHERE vendor_id = NVL(&vendor_id_NULL_for_all_vendors, vendor_id);



1.Migration script for generating ETDS flat file:


Take "TDS 2.244% Sec. 194(C)" tax and assume that the total tds rate given is : 2.244% which includes 10% surcharge and 2% cess. And if the user had already entered the value: 2.244 in the field 'Percentage' and no value in the field 'Surcharge %age',Then he should query that particular tax code and give the value 0.244 in the
'Surcharge %age' field.


2.Run scripts present in the "misc" folder:


a. sqlplus apps/ @jai_ap_etds_4353842_mig1_apps.sql

b. SELECT tax_id , tax_name , tax_Rate , surcharge_rate
FROM JA_IN_TAX_CODE_APP;
Based on the above data, you might want to delete some taxes (as discussed with functional user) which do not contain cess in the tax rate.


DELETE JA_IN_TAX_CODE_APP
WHERE tax_id = &relevant_tax_id_from_query_above


c.sqlplus apps/ @jai_ap_etds_4353842_mig2_apps.sql
ci.

RG1 - Source Migration for Shipment / Manual AR Invoice Records :

Sqlplus apps/apps@ jai_mig_4346220.sql


output has 40>0
output iv has some value >

select * from ja_in_localization_errors where application_id = '4346220'


Select * from ja_in_rg_i where register_id = < register_id from above>
shipment transaction,


UPDATE ja_in_rg_i SET cess_amount = ( SELECT sum(func_tax_amount) FROM
ja_in_so_picking_lines jspl , ja_in_so_picking_tax_lines jsptl ,
ja_in_tax_codes jtc WHERE jspl.delivery_detail_id = jsptl.delivery_detail_id AND jspl.delivery_detail_id = <&reference_no from RG1 balance screen> AND jspl.organization_id = &organization_id AND jspl.location_id= &location_id AND jspl.inventory_item_id = &inventory_item_id AND jspl.quantity = &rg1_qty AND jtc.tax_id = jsptl.tax_id AND upper(jtc.tax_type) IN ('EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS')), source = 'WSH',
last_update_date = sysdate, last_updated_by = -4346220
WHERE register_id = ®ister_id;

commit;



AR Receivable transaction:

UPDATE ja_in_rg_i SET cess_amount = ( SELECT sum(func_tax_amount)
FROM ja_in_ra_customer_trx jrct, ja_in_ra_customer_trx_lines jrctl,
ja_in_ra_cust_trx_tax_lines jrcttl , ja_in_tax_codes jtc WHERE
jrct.customer_trx_id = jrctl.customer_trx_id AND jrct.customer_trx_id = <&reference_no from RG1 balance screen>
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrct.organization_id = &organization_id AND jrct.location_id = &location_id AND jrctl.inventory_item_id = inventory_item_id AND jtc.tax_id = jrcttl.tax_id AND upper(jtc.tax_type) IN ('EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS')
source = 'AR', last_update_date = sysdate ,
last_updated_by = -4346220 WHERE register_id = ®ister_id;

commit;





perl compile_il_apps_obj.pl appspwd=apps


Go to "Application Developer" Responsibility
- Open View -> Requests
- Click on "Submit New Request"
- Click on "Single Request"
- Enter "Generate Messages" in Name field
- Enter the following parameters
Language : US
Application : Asia/Pacific Localizations
Mode : DB_TO_RUNTIME
- Click Ok and then Submit the request, ensure that request completes successfully
- Logout and Relogin




CUSTOM.pll on $AU_TOP/resource.

Cp /erpp/erppappl/ja/5498551/patch/backup/resource/CUSTOM.pll /erpp/erppappl/au/11.5.0/resource/CUSTOM.pll

f60gen module=CUSTOM userid=apps/apps module_type=LIBRARY





adadminchange maintenance mode Disable maintenance mode


sqlplus apps/apps


EXEC SYS.UTL_RECOMP.RECOMP_PARALLEL(8);

Start the application server.