[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| based on the parameters selected. |
| |
+===========================================================================*/
PROCEDURE POPULATE_DATA (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_from_organization_code IN VARCHAR2,
p_to_organization_code IN VARCHAR2,
p_from_item IN VARCHAR2 ,
p_to_item IN VARCHAR2,
p_user_item_type IN VARCHAR2,
p_created_n_days IN NUMBER,
p_updated_n_days IN NUMBER
)IS
l_SQLQuery VARCHAR2(12000) := ' SELECT '||
'B.INVENTORY_ITEM_ID ITEM_ID,'||
'WSH_UTIL_CORE.GET_ITEM_NAME (B.INVENTORY_ITEM_ID, B.ORGANIZATION_ID) PRODUCT_CODE, '||
'T.DESCRIPTION DESCRIPTION, '||
'T.LONG_DESCRIPTION LONG_DESCRIPTION, '||
'POHC.HAZARD_CLASS HAZARD_CLASS, '||
'B.ORGANIZATION_ID ORGANIZATION_ID, '||
'B.SOURCE_ORGANIZATION_ID SRC_ORGANIZATION_ID,'||
'FLV.MEANING ITEM_TYPE, '||
'B.PRIMARY_UOM_CODE UNIT_OF_MEASURE, '||
'B.LIST_PRICE_PER_UNIT ITEM_VALUE, '||
'GLPV.CURRENCY_CODE INCOMING_CURRENCY, '||
'HL.COUNTRY COUNTRY, '||
' B.ATTRIBUTE1 , B.ATTRIBUTE2 , B.ATTRIBUTE3 , B.ATTRIBUTE4 , '||
' B.ATTRIBUTE5 , B.ATTRIBUTE6 , B.ATTRIBUTE7 , B.ATTRIBUTE8 ,'||
' B.ATTRIBUTE9 , B.ATTRIBUTE10, B.ATTRIBUTE11, B.ATTRIBUTE12,'||
' B.ATTRIBUTE13, B.ATTRIBUTE14, B.ATTRIBUTE15, '||
' OOD.OPERATING_UNIT '||
' FROM '||
' MTL_SYSTEM_ITEMS_TL T , '||
' MTL_SYSTEM_ITEMS_B B, '||
' HR_ALL_ORGANIZATION_UNITS HU,'||
' HR_LOCATIONS HL, '||
' GL_LEDGERS_PUBLIC_V GLPV, '||
' PO_HAZARD_CLASSES_TL POHC, '||
' FND_LANGUAGES FNDL, '||
' FND_LOOKUP_VALUES FLV, '||
' MTL_PARAMETERS MP, '||
' ORG_ORGANIZATION_DEFINITIONS OOD '||
'WHERE '||
' B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID '||
' AND B.ORGANIZATION_ID = T.ORGANIZATION_ID '||
' AND HU.LOCATION_ID = HL.LOCATION_ID '||
' AND HU.ORGANIZATION_ID = B.ORGANIZATION_ID '||
' AND GLPV.LEDGER_ID = OOD.SET_OF_BOOKS_ID '||
' AND POHC.HAZARD_CLASS_ID(+) = B.HAZARD_CLASS_ID'||
' AND FNDL.INSTALLED_FLAG = ''B'' '||
' AND B.INVENTORY_ITEM_STATUS_CODE <> ''Inactive'' '||
' AND FNDL.LANGUAGE_CODE = T.LANGUAGE '||
' AND FNDL.LANGUAGE_CODE = NVL(POHC.LANGUAGE,FNDL.LANGUAGE_CODE) '||
' AND FLV.LOOKUP_TYPE = ''ITEM_TYPE'' '||
' AND FLV.LANGUAGE = FNDL.LANGUAGE_CODE '||
' AND FLV.VIEW_APPLICATION_ID = 3 '||
' AND FLV.LOOKUP_CODE = B.ITEM_TYPE'||
' AND B.ORGANIZATION_ID = MP.ORGANIZATION_ID '||
' AND OOD.ORGANIZATION_ID = MP.ORGANIZATION_ID' ;
WSH_DEBUG_SV.log(l_module_name,'P_UPDATED_N_DAYS',P_UPDATED_N_DAYS);
IF p_updated_n_days IS NOT NULL THEN
l_Item_Condn5Tab(1).g_number_val := p_updated_n_days;
l_Item_Condn5Tab(1).g_Bind_Literal := ':b_updated_n_days';
WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, ' AND B.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Item_Condn5Tab, 'NUMBER');
select WSH_ITM_REQUEST_CONTROL_S.NEXTVAL
into
l_num_ReqCtrl_tab(l_num_ReqCtrl_tab.COUNT + 1)
from dual;
SELECT LANGUAGE_CODE INTO l_LanguageCode FROM
FND_LANGUAGES WHERE
INSTALLED_FLAG = 'B';
WSH_DEBUG_SV.LOG (l_module_name, 'Number of Request Controls to be inserted : ' , l_num_ReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
INSERT INTO WSH_ITM_REQUEST_CONTROL(
REQUEST_CONTROL_ID,
APPLICATION_ID,
PROCESS_FLAG,
SERVICE_TYPE_CODE,
ORIGINAL_SYSTEM_REFERENCE,
LANGUAGE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES(
l_num_ReqCtrl_tab(i),
702,
0,
'ITEM_SYNC',
l_num_invItemID_tab(i),
l_LanguageCode,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id
);
WSH_DEBUG_SV.LOG (l_module_name, 'Number of Items to be inserted : ' , l_num_ItmReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
INSERT INTO WSH_ITM_ITEMS (
ITEM_ID,
REQUEST_CONTROL_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
PRODUCT_CODE,
SRC_ORGANIZATION_ID,
DESCRIPTION,
LONG_DESCRIPTION,
HAZARD_CLASS,
ITEM_TYPE,
UNIT_OF_MEASURE,
ITEM_VALUE,
INCOMING_CURRENCY,
COUNTRY,
ATTRIBUTE1_VALUE,
ATTRIBUTE2_VALUE,
ATTRIBUTE3_VALUE,
ATTRIBUTE4_VALUE,
ATTRIBUTE5_VALUE,
ATTRIBUTE6_VALUE,
ATTRIBUTE7_VALUE,
ATTRIBUTE8_VALUE,
ATTRIBUTE9_VALUE,
ATTRIBUTE10_VALUE,
ATTRIBUTE11_VALUE,
ATTRIBUTE12_VALUE,
ATTRIBUTE13_VALUE,
ATTRIBUTE14_VALUE,
ATTRIBUTE15_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OPERATING_UNIT
)
VALUES(
WSH_ITM_ITEMS_S.NEXTVAL,
l_num_ItmReqCtrl_tab(i),
l_num_invItem_tab(i),
l_num_orgId_tab(i),
l_varchar_PrdCode_tab(i),
l_num_srcOrgId_tab(i),
l_varchar_Desc_tab(i),
l_varchar_LongDesc_tab(i),
l_varchar_hazClass_tab(i),
l_varchar_ItmType_tab(i),
l_varchar_UOM_tab(i),
l_num_itmVal_tab(i),
l_varchar_Curr_tab(i),
l_varchar_Coun_tab(i),
l_varchar_Attrib1_tab(i),
l_varchar_Attrib2_tab(i),
l_varchar_Attrib3_tab(i),
l_varchar_Attrib4_tab(i),
l_varchar_Attrib5_tab(i),
l_varchar_Attrib6_tab(i),
l_varchar_Attrib7_tab(i),
l_varchar_Attrib8_tab(i),
l_varchar_Attrib9_tab(i),
l_varchar_Attrib10_tab(i),
l_varchar_Attrib11_tab(i),
l_varchar_Attrib12_tab(i),
l_varchar_Attrib13_tab(i),
l_varchar_Attrib14_tab(i),
l_varchar_Attrib15_tab(i),
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
l_num_Operunit_tab(i)
);