[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT attribute_value , template_id , rgm_item_regns_id
FROM jai_rgm_item_attrib_v
WHERE attribute_code = p_attribute_code
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND regime_code = p_regime_code;
SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
copied, assigned, deleted or imported in the base item form.
Fix:-
To support this functionality a procedure PROPAGATE_ITEM_ACTION is developed
which accepts the base action as well as pl/sql table as inventory items to be processed.
The procedure will be able to process items in bulk so that only one call from base item
form can propagate changes in many items. Each row in plsql table PT_ITEM_DATA will be
a comman (,) seperated string depending upon the PV_ACTION_TYPE argument.
(For more details regarding possible values for each argument and format of the string
for each PV_ACTION_TYPE argument please refere the bug# 4389149)
3. 08-Jun-2005 Version 116.3 jai_inv_items -Object is Modified to refer to New DB Entity names in place of
Old DB Entity Names as required for CASE COMPLAINCE.
4. 13-Jun-2005 File Version: 116.4
Ramananda for bug#4428980. Removal of SQL LITERALs is done
5. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
6. 15-Jul-2005 Brathod, For Bug# 4496223 Version 117.2
Issue: -
The Code hook API for IL Item currently accepts datatype of the type
table_item which is pl-sql table of varchar2(100). But as the code hook
needs to be called by base application it should not have any depedancy on IL
Product. Use of this data type introduces the dependancy of IL.
Solution:-
To avoid this the datatype should be independent of product. The pl-sql table
type aregument is removed from PROPAGATE_ITEM_ACTION procedure and added
the following four simple arguments
1. pn_organization_id - NUMBER - Destination Organization
2. pn_inventory_item_id - NUMBER - Destination Inventory Item
3. pn_source_organization_id - NUMBER - Source Organization
4. pn_source_inventory_item_id - NUMBER - Source Inventory Item
7. 16-Aug-2005 Brathod, For Bug#4554851, File Version 120.3
Issue :- Item Classification form allows multiple template assign ment
for same organization item combination
Solution:- Added a regime_code condition in cursor cur_get_itm_attribs
to fetch only attribute value for EXCISE regime.
Dependency
----------
JAIITMCL.fmb (120.5)
8. 31-01-2007 SACSETHI , FOR BUG#5631784 , File Version #120.4
FORWARD PORTING BUG FROM 11I BUG 4742259
NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
Changes -
Object Type Object Name Change Description
-----------------------------------------------------------------------------------------------------
VARIABLE LN_RGM_ITEM_REGNS_ID NEW FOR TCS TO CHECK ITEMS REGIME ID
VARIABLE LN_TEMPLATE_ID NEW FOR TEMPLATE_ID
VARIABLE LV_ITEM_NAME NEW ITEM NAME USED TO SEND TO CALLING OBJECT
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
-------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
Of File On Bug/Patchset
jai_inv_items_pkg_b.sql
--------------------------------------------------------------------------------------------------------------------------------------------------
115.0 4245134 IN60105D2 VAT Objects 115.0 Brathod 17-Mar-2005 Technical Dependacny
+4245089
--------------------------------------------------------------------------------------------------------------------------------------------------*/
IF p_regime_code IS NULL THEN
p_process_flag := jai_constants.expected_error;
INSERT INTO JAI_RGM_ITM_TEMPLATES
(
template_id
, template_name
, description
, regime_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(
--ln_template_id
JAI_RGM_ITM_TEMPLATES_S.NEXTVAL /* Modified by Ramananda for removal of SQL LITERALs */
, p_template_name
, p_description
, p_regime_code
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
)
RETURNING template_id INTO ln_template_id;
SELECT inventory_item_id
FROM JAI_INV_ITM_SETUPS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT templ_org_regns_id
FROM JAI_RGM_TMPL_ORG_REGNS torg
WHERE torg.organization_id = p_organization_id
AND torg.template_id = p_template_id;
SELECT templ_itm_regns_id
FROM JAI_RGM_TMPL_ITM_REGNS tirg
WHERE tirg.templ_org_regns_id = p_templ_org_regns_id
AND tirg.inventory_item_id = p_inv_itm_id;
SELECT JAI_RGM_TMPL_ORG_REGNS_S.NEXTVAL
INTO p_templ_org_regns_id
FROM DUAL;
INSERT INTO JAI_RGM_TMPL_ORG_REGNS
( templ_org_regns_id
, template_id
, organization_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(
p_templ_org_regns_id
, p_templ_id
, p_org_id
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
);
INSERT INTO JAI_RGM_TMPL_ITM_REGNS
(
templ_itm_regns_id
, templ_org_regns_id
, inventory_item_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES (
JAI_RGM_TMPL_ITM_REGNS_S.nextval
, p_templ_org_regns_id
, p_inv_itm_id
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
);
SELECT rgm_item_regns_id
FROM JAI_RGM_ITM_REGNS rirg
WHERE rirg.regime_code = p_regime_code
AND rirg.organization_id = p_organization_id
AND rirg.inventory_item_id = p_inventory_item_id;
* Item specific registration does not exist so create one by inserting a row in
* JAI_RGM_ITM_REGNS
*/
--SELECT JAI_RGM_ITM_REGNS_S.NEXTVAL INTO ln_rgm_item_regns_id FROM DUAL;
INSERT INTO JAI_RGM_ITM_REGNS
( rgm_item_regns_id
,regime_code
,organization_id
,inventory_item_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
)
VALUES(
--ln_rgm_item_regns_id
JAI_RGM_ITM_REGNS_S.NEXTVAL /* Modified by Ramananda for removal of SQL LITERALs */
, p_regime_code
, p_organization_id
, p_inventory_item_id
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
) returning rgm_item_regns_id into ln_rgm_item_regns_id;
INSERT INTO JAI_RGM_ITM_TMPL_ATTRS(
ITM_TEMPL_ATTRIBUTE_ID,
TEMPLATE_ID,
RGM_ITEM_REGNS_ID,
ATTRIBUTE_CODE,
ATTRIBUTE_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY
) VALUES (JAI_RGM_ITM_TMPL_ATTRS_S.nextval
,p_template_id
,p_rgm_item_regns_id
,p_tab_attributes(ln_attrib).attribute_code
,p_tab_attributes(ln_attrib).attribute_value
,SYSDATE, fnd_global.user_id , SYSDATE, fnd_global.login_id, fnd_global.user_id
);
select
jmsi.rowid,
jmsi.excise_flag ,
jmsi.item_class ,
jmsi.modvat_flag ,
jmsi.item_tariff ,
jmsi.item_folio ,
jmsi.item_trading_flag,
jmsi.organization_id,
jmsi.inventory_item_id
from JAI_INV_ITM_SETUPS jmsi
where (
(cpn_synchronization_number is null)
or
( (cpn_synchronization_number is not null ) and (synchronization_number = cpn_synchronization_number ) )
);
select attribute_code,
attribute_value,
last_updated_by
from jai_rgm_item_attrib_v
where organization_id = cpn_organization_id
and inventory_item_id = cpn_inventory_item_id
AND regime_code = jai_constants.excise_regime ;
/* Update JAI_INV_ITM_SETUPS if atleast one attribute is changed */
if nvl(lv_new_excise_flag, 'NULL') <> nvl(rec_jmsi.excise_flag, 'NULL') or
nvl(lv_new_item_class, 'NULL') <> nvl(rec_jmsi.item_class, 'NULL') or
nvl(lv_new_modvat_flag, 'NULL') <> nvl(rec_jmsi.modvat_flag, 'NULL') or
nvl(lv_new_item_tariff, 'NULL') <> nvl(rec_jmsi.item_tariff, 'NULL') or
nvl(lv_new_item_folio, 'NULL') <> nvl(rec_jmsi.item_folio, 'NULL') or
nvl(lv_new_item_trading_flag, 'NULL') <> nvl(rec_jmsi.item_trading_flag, 'NULL')
then
update JAI_INV_ITM_SETUPS
set excise_flag = lv_new_excise_flag
, item_class = lv_new_item_class
, modvat_flag = lv_new_modvat_flag
, item_tariff = lv_new_item_tariff
, item_folio = lv_new_item_folio
, item_trading_flag = lv_new_item_trading_flag
, last_update_date = sysdate
, last_updated_by = 0405051/* p_synchronization_number*/
where rowid = rec_jmsi.rowid;
end if; /* update */
/* India Localization code hook for Base item copy/delete/assignment/import action */
PROCEDURE propagate_item_action
(
pv_action_type IN VARCHAR2
, pn_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
, pn_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
, pn_source_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
, pn_source_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
, pn_set_process_id IN NUMBER
, pv_called_from IN VARCHAR2
)
IS
CURSOR cur_get_items_from_interface
IS
SELECT intf.organization_id organization_id
,intf.inventory_item_id inventory_item_id
,master_org.master_organization_id source_organization_id
FROM mtl_system_items_interface intf
,mtl_parameters master_org
WHERE intf.process_flag = 7
AND intf.transaction_type = 'CREATE'
AND intf.request_id = fnd_global.conc_request_id
AND intf.set_process_id = pn_set_process_id
AND intf.organization_id = master_org.organization_id
AND intf.organization_id <> master_org.master_organization_id ;
ELSE /* pv_action_type is either COPY, ASSIGN or DELETE */
IF pv_action_type IN ('COPY','ASSIGN') THEN
IF pn_organization_id IS NULL
OR pn_inventory_item_id IS NULL
OR pn_source_organization_id IS NULL
OR pn_source_inventory_item_id IS NULL THEN
fnd_message.set_name('JA','JAI_IL_API_ARGS_NOT_PROPER');
ELSIF pv_action_type IN ('DELETE') THEN
IF pn_organization_id IS NULL
OR pn_inventory_item_id IS NULL THEN
fnd_message.set_name('JA','JAI_IL_API_ARGS_NOT_PROPER');
jai_inv_items_pkg.delete_items
(
pn_organization_id => pn_organization_id
,pn_inventory_item_id => pn_inventory_item_id
);
SELECT rgm_item_regns_id
FROM JAI_RGM_ITM_REGNS
WHERE regime_code = cpv_regime_code
AND organization_id = cpn_organization_id
AND inventory_item_id = cpn_inventory_item_id;
SELECT template_id
,rgm_item_regns_id
,attribute_code
,attribute_value
FROM JAI_RGM_ITM_TMPL_ATTRS
WHERE (( template_id = cpn_itm_templ_id AND cpv_itm_templ_flg = 'T')
OR
( rgm_item_regns_id = cpn_itm_templ_id AND cpv_itm_templ_flg = 'I')
);
SELECT torg.template_id
FROM JAI_RGM_TMPL_ITM_REGNS tirg
,JAI_RGM_TMPL_ORG_REGNS torg
,JAI_RGM_ITM_TEMPLATES rgtmp
WHERE tirg.templ_org_regns_id = torg.templ_org_regns_id
AND torg.template_id = rgtmp.template_id
AND torg.organization_id = cpn_organization_id
AND tirg.inventory_item_id = cpn_inventory_item_id
AND rgtmp.regime_code = cpv_regime_code;
lt_attribs.delete; -- Flush plsql table
INSERT INTO JAI_INV_ITM_SETUPS
(
inventory_item_id
, organization_id
, item_class
, modvat_flag
, item_tariff
, item_folio
, excise_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, item_trading_flag
, synchronization_number
)
(SELECT
ln_inventory_item_id
, ln_organization_id
, item_class
, modvat_flag
, item_tariff
, item_folio
, excise_flag
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, item_trading_flag
, NULL
FROM JAI_INV_ITM_SETUPS
WHERE organization_id = ln_source_organization_id
AND inventory_item_id = ln_source_inventory_item_id
);
PROCEDURE delete_items ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
)
IS
ln_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE ;
lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.delete_items';
SELECT 1
FROM JAI_RGM_TMPL_ITM_REGNS
WHERE templ_org_regns_id = cpn_templ_org_regns_id;
DELETE FROM JAI_RGM_ITM_REGNS
WHERE inventory_item_id = ln_inventory_item_id
AND organization_id = ln_organization_id
AND regime_code = jai_constants.excise_regime
RETURNING rgm_item_regns_id INTO ln_rgm_item_regns_id;
DELETE FROM JAI_RGM_ITM_TMPL_ATTRS
WHERE rgm_item_regns_id = ln_rgm_item_regns_id;
DELETE FROM JAI_RGM_TMPL_ITM_REGNS
WHERE templ_itm_regns_id IN (SELECT templ_itm_regns_id
FROM JAI_RGM_TMPL_ITM_REGNS tirg
,JAI_RGM_TMPL_ORG_REGNS torg
,JAI_RGM_ITM_TEMPLATES rgtmp
WHERE tirg.templ_org_regns_id = torg.templ_org_regns_id
AND torg.template_id = rgtmp.template_id
AND tirg.inventory_item_id = ln_inventory_item_id
AND torg.organization_id = ln_organization_id
AND rgtmp.regime_code = jai_constants.excise_regime
)
RETURNING templ_org_regns_id INTO ln_templ_org_regns_id ;
then delete the template organizatin registration also
*/
OPEN cur_chk_templ_itm_regns (cpn_templ_org_regns_id => ln_templ_org_regns_id);
DELETE FROM JAI_RGM_TMPL_ORG_REGNS
WHERE templ_org_regns_id = ln_templ_org_regns_id;
DELETE FROM JAI_INV_ITM_SETUPS
WHERE organization_id = ln_organization_id
AND inventory_item_id = ln_inventory_item_id;
END delete_items;
/* -----------------------END OF PROCEDURE DELETE_ITEMS ----------------------------*/
/* End of Bug# 4389149 */
end jai_inv_items_pkg;