[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Private procedure that inserts/updates Expense/Labor items for an org
and calls markdirty for all inserted records. Bug 3724165
*/
PROCEDURE INSERT_ACC_REC_MARKDIRTY_EXP( p_organization_id IN NUMBER
, p_resource_id IN NUMBER
, p_old_org_id IN NUMBER)
IS
l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
UPDATE jtm_mtl_system_items_acc
SET counter = counter + 1
, last_update_date = SYSDATE
, last_updated_by = 1
WHERE resource_id = p_resource_id
AND (inventory_item_id, organization_id)
IN (SELECT inventory_item_id, organization_id
FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
WHERE organization_id = p_organization_id
and msi.material_billable_flag = cbtc.billing_type (+)
AND cbtc.billing_category IN ('E','L'));
, 'Entering INSERT_ACC_REC_MARKDIRTY_EXP'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
/*Block insert every item from given org not yet in acc table*/
SELECT jtm_acc_table_s.NEXTVAL, inventory_item_id, organization_id,
p_resource_id
BULK COLLECT INTO
l_tab_access_id, items, organizations, l_tab_resource_id
FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
WHERE ( inventory_item_id, organization_id ) NOT IN (
SELECT inventory_item_id, organization_id
FROM jtm_mtl_system_items_acc
WHERE resource_id = p_resource_id )
AND msi.material_billable_flag = cbtc.billing_type (+)
AND cbtc.billing_category IN ('E', 'L')
AND organization_id = p_organization_id;
' inserted record(s) to resource: '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO jtm_mtl_system_items_acc(
access_id, last_update_date, last_updated_by, creation_date,
created_by , counter, resource_id, inventory_item_id, organization_id)
VALUES (
l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
items(i), organizations(i));
END IF; -- End of Insert of Expense and Labor items
, 'Leaving INSERT_ACC_REC_MARKDIRTY_EXP'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'INSERT_ACC_REC_MARKDIRTY_EXP'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END INSERT_ACC_REC_MARKDIRTY_EXP;
/*** Private procedure that inserts given item related data for resource ***/
PROCEDURE Insert_ACC_Record
( p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_resource_id IN NUMBER
)
IS
--Bug 3908277 - Static Query converted into Cursor.
CURSOR c_billCat(b_inventory_item_id NUMBER, b_organization_id NUMBER)
IS
SELECT billing_category
FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
WHERE msi.material_billable_flag = cbtc.billing_type (+)
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
, 'Entering Insert_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'Inserting ACC record for resource_id = ' || p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Insert item ACC record ***/
IF l_billCat = 'E' OR l_billCat = 'L' THEN
JTM_HOOK_UTIL_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_explab_publication_item_name
, P_ACC_TABLE_NAME => g_acc_table_name
, P_RESOURCE_ID => p_resource_id
, P_PK1_NAME => g_pk1_name
, P_PK1_NUM_VALUE => p_inventory_item_id
, P_PK2_NAME => g_pk2_name
, P_PK2_NUM_VALUE => p_organization_id
);
JTM_HOOK_UTIL_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
, P_ACC_TABLE_NAME => g_acc_table_name
, P_RESOURCE_ID => p_resource_id
, P_PK1_NAME => g_pk1_name
, P_PK1_NUM_VALUE => p_inventory_item_id
, P_PK2_NAME => g_pk2_name
, P_PK2_NUM_VALUE => p_organization_id
);
CSL_MTL_SEC_LOCATORS_ACC_PKG.Insert_Secondary_Locators
( p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_resource_id => p_resource_id
);
, 'Leaving Insert_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
END Insert_ACC_Record;
/*** Private procedure that deletes given item related data for resource ***/
PROCEDURE Delete_ACC_Record
( p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_resource_id IN NUMBER
)
IS
--Bug 3908277 - Static Query converted into Cursor.
CURSOR c_billCat(b_inventory_item_id NUMBER, b_organization_id NUMBER)
IS
SELECT billing_category
FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
WHERE msi.material_billable_flag = cbtc.billing_type (+)
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
, 'Entering Delete_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
/*** Delete item ACC record ***/
IF l_billCat = 'E' OR l_billCat = 'L' THEN
JTM_HOOK_UTIL_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_explab_publication_item_name
, P_ACC_TABLE_NAME => g_acc_table_name
, P_RESOURCE_ID => p_resource_id
, P_PK1_NAME => g_pk1_name
, P_PK1_NUM_VALUE => p_inventory_item_id
, P_PK2_NAME => g_pk2_name
, P_PK2_NUM_VALUE => p_organization_id
);
JTM_HOOK_UTIL_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
, P_ACC_TABLE_NAME => g_acc_table_name
, P_RESOURCE_ID => p_resource_id
, P_PK1_NAME => g_pk1_name
, P_PK1_NUM_VALUE => p_inventory_item_id
, P_PK2_NAME => g_pk2_name
, P_PK2_NUM_VALUE => p_organization_id
);
CSL_MTL_SEC_LOCATORS_ACC_PKG.Delete_Secondary_Locators
( p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_resource_id => p_resource_id
);
, 'Leaving Delete_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
END Delete_ACC_Record;
Public function that gets called when a system item needs to be inserted into ACC table.
***/
PROCEDURE Pre_Insert_Child
( p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_resource_id IN NUMBER
)
IS
BEGIN
/*** get debug level ***/
g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
, 'Entering Pre_Insert_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
/*** yes -> insert system item in acc record ***/
Insert_ACC_Record
( p_inventory_item_id
, p_organization_id
, p_resource_id
);
, 'Leaving Pre_Insert_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
END Pre_Insert_Child;
to be deleted from ACC table.
***/
PROCEDURE Post_Delete_Child
( p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_resource_id IN NUMBER
)
IS
l_acc_id NUMBER;
, 'Entering Post_Delete_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
Delete_ACC_Record
( p_inventory_item_id
, p_organization_id
, p_resource_id
);
, 'Leaving Post_Delete_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
END Post_Delete_Child;
/*Private procedure that inserts a record into CSL_RESOURCE_INVENTORY_ORG*/
PROCEDURE INSERT_RESOURCE_PROFILE_REC( p_resource_id IN NUMBER
, p_organization_id IN NUMBER
, p_category_set_id IN NUMBER
, p_category_id IN NUMBER )
IS
BEGIN
INSERT INTO CSL_RESOURCE_INVENTORY_ORG ( RESOURCE_ID, ORGANIZATION_ID, LAST_UPDATE_DATE
, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, CATEGORY_SET_ID, CATEGORY_ID )
VALUES ( p_resource_id, p_organization_id
, SYSDATE, 1, SYSDATE, 1, p_category_set_id, p_category_id );
END INSERT_RESOURCE_PROFILE_REC;
/*Private procedure that updates a record in CSL_RESOURCE_INVENTORY_ORG*/
PROCEDURE UPDATE_RESOURCE_PROFILE_REC( p_resource_id IN NUMBER
, p_organization_id IN NUMBER
, p_category_set_id IN NUMBER
, p_category_id IN NUMBER )
IS
BEGIN
UPDATE CSL_RESOURCE_INVENTORY_ORG
SET ORGANIZATION_ID = p_organization_id
, CATEGORY_SET_ID = p_category_set_id
, CATEGORY_ID = p_category_id
, LAST_UPDATE_DATE = SYSDATE
WHERE RESOURCE_ID = p_resource_id;
END UPDATE_RESOURCE_PROFILE_REC;
/*Private procedure that deletes a record from CSL_RESOURCE_INVENTORY_ORG*/
PROCEDURE DELETE_RESOURCE_PROFILE_REC( p_resource_id IN NUMBER )
IS
BEGIN
DELETE CSL_RESOURCE_INVENTORY_ORG
WHERE RESOURCE_ID = p_resource_id;
END DELETE_RESOURCE_PROFILE_REC;
/*Delete all acc records for resource without markdirty */
PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2 )
IS
BEGIN
/*** get debug level ***/
g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
, 'Entering DELETE_ALL_ACC_RECORDS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
/*Do the actual delete*/
DELETE JTM_MTL_SYSTEM_ITEMS_ACC
WHERE RESOURCE_ID = p_resource_id;
DELETE CSL_MTL_SECONDARY_LOCATORS_ACC
WHERE RESOURCE_ID = p_resource_id;
/*Delete the resource from CSL_RESOURCE_INVENTORY_ORG*/
DELETE_RESOURCE_PROFILE_REC( p_resource_id => p_resource_id );
, 'Leaving DELETE_ALL_ACC_RECORDS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'DELETE_ALL_ACC_RECORDS'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END DELETE_ALL_ACC_RECORDS;
PROCEDURE INSERT_ALL_ACC_RECORDS( p_resource_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2 )
IS
CURSOR c_mobile_resp ( b_resource_id NUMBER ) IS
SELECT usr.user_id
, usrresp.responsibility_id
, usrresp.responsibility_application_id
FROM asg_pub pub
, asg_pub_responsibility pubresp
, fnd_user_resp_groups usrresp
, fnd_user usr
, jtf_rs_resource_extns res
, asg_user au
WHERE res.resource_id = b_resource_id
AND pub.name = 'SERVICEL'
AND pub.enabled='Y'
AND pub.status='Y'
AND pub.pub_id = pubresp.pub_id
AND pubresp.responsibility_id = usrresp.responsibility_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usrresp.start_date,sysdate))
AND TRUNC(NVL(usrresp.end_date,sysdate))
AND usrresp.user_id = usr.user_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usr.start_date,sysdate))
AND TRUNC(NVL(usr.end_date,sysdate))
AND usr.user_id = res.user_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active,sysdate))
AND TRUNC(NVL(res.end_date_active,sysdate));
, 'Entering INSERT_ALL_ACC_RECORDS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
/*Bug 3929942 - Removed the code to update existing SIs in the ACC table,
as this proc is called only during user creation, and during user creation
the ACC table will be empty for the mobile resource being created*/
--Bug 3724165 - Get only Material Items when applying Category Set Filter.
/*Block insert every item from given org not yet in acc table - Material*/
--Bug 3929942 - Added Hints and use bind variables
l_stmt := 'INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC (';
l_stmt := l_stmt || ' access_id, last_update_date, last_updated_by, ';
l_stmt := l_stmt || ' SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_N4)*/ ';
l_stmt := l_stmt || ' SELECT /*+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)*/ INVENTORY_ITEM_ID, ORGANIZATION_ID';
l_stmt := l_stmt || ' (SELECT inventory_item_id';
/*Block insert every item from given org not yet in acc table - Expense and Labor*/
--Bug 3929942 - Added Hints
INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC(access_id, last_update_date, last_updated_by,
creation_date, created_by, counter, resource_id,inventory_item_id, organization_id )
SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_N4)*/ jtm_acc_table_s.NEXTVAL, SYSDATE, 1,
SYSDATE, 1, 1, p_resource_id, inventory_item_id, organization_id
FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
WHERE organization_id = l_profile_org_id
AND msi.material_billable_flag = cbtc.billing_type (+)
AND cbtc.billing_category IN ('E','L')
AND ( inventory_item_id, organization_id )
NOT IN (
SELECT /*+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)*/ inventory_item_id,
organization_id
FROM jtm_mtl_system_items_acc acc
WHERE resource_id = p_resource_id );
/*Delete any old record for resource from CSL_RESOURCE_INVENTORY_ORG*/
DELETE_RESOURCE_PROFILE_REC( p_resource_id => p_resource_id );
/*Insert resource org record*/
INSERT_RESOURCE_PROFILE_REC( p_resource_id => p_resource_id
, p_organization_id => l_profile_org_id
, p_category_set_id => l_profile_category_set_id
, p_category_id => l_profile_category_id );
, 'Leaving INSERT_ALL_ACC_RECORDS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'INSERT_ALL_ACC_RECORDS'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END INSERT_ALL_ACC_RECORDS;
Private procedure that inserts (new) system items for an org/category
and calls markdirty for all inserted records.
*/
PROCEDURE INSERT_ACC_REC_MARKDIRTY( p_organization_id IN NUMBER
, p_category_set_id IN NUMBER
, p_category_id IN NUMBER
, p_resource_id IN NUMBER
, p_last_run_date IN DATE
, p_changed IN VARCHAR2
, p_old_org_id IN NUMBER )
IS
--Bug 3724165 - To take care of this bug, this procedure will only take
--care of Material Items. Expense and Labor items will be taken care of
--by the procedure INSERT_ACC_REC_MARKDIRTY_EXP
l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM JTM_MTL_SYSTEM_ITEMS_ACC
WHERE RESOURCE_ID = b_resource_id )
AND ORGANIZATION_ID = b_organization_id
AND material_billable_flag = billing_type (+)
AND NVL(cbtc.billing_category, 'M') = 'M'
AND (b_changed = 'Y'
OR msi.CREATION_DATE >= NVL(b_last_run_date, msi.CREATION_DATE ));
SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM JTM_MTL_SYSTEM_ITEMS_ACC
WHERE RESOURCE_ID = b_resource_id )
AND ORGANIZATION_ID = b_organization_id
AND material_billable_flag = billing_type (+)
AND NVL(cbtc.billing_category, 'M') = 'M'
AND inventory_item_id IN
(SELECT inventory_item_id
FROM mtl_item_categories itemcat
WHERE itemcat.category_id = b_category_id
AND itemcat.organization_id = b_organization_id
AND (b_changed = 'Y'
OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)));
SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID,
p_resource_id, material_billable_flag
FROM MTL_SYSTEM_ITEMS_B msi
WHERE NOT EXISTS (
SELECT 1
FROM JTM_MTL_SYSTEM_ITEMS_ACC acc
WHERE RESOURCE_ID = b_resource_id
AND msi.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
AND msi.ORGANIZATION_ID = acc.ORGANIZATION_ID)
AND NVL(MATERIAL_BILLABLE_FLAG, 'M') = 'M'
AND ORGANIZATION_ID = b_organization_id
AND inventory_item_id IN
(SELECT inventory_item_id
FROM mtl_item_categories itemcat
WHERE itemcat.category_set_id = b_category_set_id
AND itemcat.organization_id = b_organization_id
AND (b_changed = 'Y'
OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)));
SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM JTM_MTL_SYSTEM_ITEMS_ACC
WHERE RESOURCE_ID = b_resource_id )
AND ORGANIZATION_ID = b_organization_id
AND material_billable_flag = billing_type (+)
AND NVL(cbtc.billing_category, 'M') = 'M'
AND inventory_item_id IN
(SELECT inventory_item_id
FROM mtl_item_categories itemcat
WHERE itemcat.category_id = b_category_id
AND itemcat.category_set_id = b_category_set_id
AND itemcat.organization_id = b_organization_id
AND (b_changed = 'Y'
OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)));
, 'Entering INSERT_ACC_REC_MARKDIRTY'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
l_stmt := 'UPDATE jtm_mtl_system_items_acc';
l_stmt := l_stmt || ' , last_update_date = SYSDATE';
l_stmt := l_stmt || ' , last_updated_by = 1';
l_stmt := l_stmt || ' (SELECT inventory_item_id, organization_id ';
l_stmt := l_stmt || ' (SELECT inventory_item_id';
l_tab_access_id.DELETE;
items.DELETE;
organizations.DELETE;
l_tab_resource_id.DELETE;
|| ' inserted record(s) to resource: '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO jtm_mtl_system_items_acc(
access_id, last_update_date, last_updated_by, creation_date,
created_by, counter, resource_id, inventory_item_id, organization_id )
VALUES (
l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
items(i), organizations(i));
l_tab_access_id.DELETE;
items.DELETE;
organizations.DELETE;
l_tab_resource_id.DELETE;
|| ' inserted record(s) to resource: '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO jtm_mtl_system_items_acc(
access_id, last_update_date, last_updated_by, creation_date,
created_by, counter, resource_id, inventory_item_id, organization_id )
VALUES (
l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
items(i), organizations(i));
l_tab_access_id.DELETE;
items.DELETE;
organizations.DELETE;
l_tab_resource_id.DELETE;
|| ' inserted record(s) to resource: '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO jtm_mtl_system_items_acc(
access_id, last_update_date, last_updated_by, creation_date,
created_by, counter, resource_id, inventory_item_id, organization_id )
VALUES (
l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
items(i), organizations(i));
l_tab_access_id.DELETE;
items.DELETE;
organizations.DELETE;
l_tab_resource_id.DELETE;
|| ' inserted record(s) to resource: '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO jtm_mtl_system_items_acc(
access_id, last_update_date, last_updated_by, creation_date,
created_by, counter, resource_id, inventory_item_id, organization_id )
VALUES (
l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,
items(i), organizations(i));
INSERT_ACC_REC_MARKDIRTY_EXP(p_organization_id, p_resource_id, p_old_org_id);
, 'Leaving INSERT_ACC_REC_MARKDIRTY'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'INSERT_ACC_REC_MARKDIRTY'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END INSERT_ACC_REC_MARKDIRTY;
that were updated since the last time the concurrent program ran.
*/
PROCEDURE UPDATE_ACC_REC_MARKDIRTY( p_last_run_date IN DATE )
IS
--Bug 3929942 - Modified the query to remove UNION
CURSOR c_changed( b_last_date DATE ) IS
SELECT /*+ INDEX (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1) index (msi MTL_SYSTEM_ITEMS_B_U1) */
acc.ACCESS_ID, acc.RESOURCE_ID, cbtc.BILLING_CATEGORY
FROM JTM_MTL_SYSTEM_ITEMS_ACC acc, MTL_SYSTEM_ITEMS_B msi
, CS_BILLING_TYPE_CATEGORIES cbtc
WHERE msi.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
AND msi.ORGANIZATION_ID = acc.ORGANIZATION_ID
AND msi.material_billable_flag = cbtc.billing_type (+)
AND msi.LAST_UPDATE_DATE >= b_last_date;
l_max_last_update_date_b DATE;
l_max_last_update_date_tl DATE;
, 'Entering UPDATE_ACC_REC_MARKDIRTY'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
/* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
/* , MTL_SYSTEM_ITEMS_TL which were custom created */
SELECT MAX(LAST_UPDATE_DATE) into l_max_last_update_date_b
FROM MTL_SYSTEM_ITEMS_B;
IF( l_max_last_update_date_b < p_last_run_date) THEN
SELECT MAX(LAST_UPDATE_DATE) into l_max_last_update_date_tl
FROM MTL_SYSTEM_ITEMS_TL;
IF(l_max_last_update_date_tl < p_last_run_date) THEN
-- No updates
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( 0
, g_table_name
, 'Leaving UPDATE_ACC_REC_MARKDIRTY'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
l_tab_access_id.DELETE;
l_tab_resource_id.DELETE;
billCat.DELETE;
accessId_Exp_Lab_Tab.DELETE;
accessId_Mat_Tab.DELETE;
resourceId_Exp_Lab_Tab.DELETE;
resourceId_Mat_Tab.DELETE;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
, 'Leaving UPDATE_ACC_REC_MARKDIRTY'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'UPDATE_ACC_REC_MARKDIRTY'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END UPDATE_ACC_REC_MARKDIRTY;
1) deletes system items for an old org/category from the client
and calls markdirty for all deleted records.
2) deletes system items from the client that are no longer present in a category
If parameter p_changed = 'Y', then scenario (1) is performed.
If parameter p_changed = 'N', then scenario (2) is performed.
*/
PROCEDURE DELETE_ALL_ACC_REC_MARKDIRTY( p_resource_id IN NUMBER
, p_organization_id IN NUMBER
, p_category_set_id IN NUMBER
, p_category_id IN NUMBER
, p_profile_org_id IN NUMBER
)
IS
BEGIN
--Obsoleted // Bug 12659742
RETURN;
END DELETE_ALL_ACC_REC_MARKDIRTY;
SELECT organization_id, category_set_id, category_id
FROM csl_resource_inventory_org
WHERE resource_id = b_resource_id;
insert all items without calling markdirty
Note that this normally should never happen since resource org
record should have been inserted during user creation (even
when the profile doesn't have a value yet)
*/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( 0
, g_table_name
, 'Resource profile record not found in csl_resource_inventory_org.' || fnd_global.local_chr(10)||
'Inserting all system item records without calling markdirty.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT_ALL_ACC_RECORDS( p_resource_id => p_resource_id
, x_return_status => l_status );
delete all old system items and insert new items with markdirty
*/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( 0
, g_table_name
, 'Inventory organization or category profiles changed.'
|| fnd_global.local_chr(10)||
'original organization_id = ' || r_org.organization_id
|| ', new organization_id = ' || l_profile_org_id
|| fnd_global.local_chr(10)||
'original category_set_id = ' || r_org.category_set_id
|| ', new category_set_id = ' || l_profile_category_set_id
|| fnd_global.local_chr(10)||
'original category_id = ' || r_org.category_id
|| ', new category_id = ' || l_profile_category_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
DELETE_ALL_ACC_REC_MARKDIRTY( p_resource_id => p_resource_id
, p_organization_id => r_org.organization_id
, p_category_set_id => r_org.category_set_id
, p_category_id => r_org.category_id
, p_profile_org_id => l_profile_org_id
);
, 'Inserting records for new profile settings'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
INSERT_ACC_REC_MARKDIRTY( p_organization_id => l_profile_org_id
, p_category_set_id => l_profile_category_set_id
, p_category_id => l_profile_category_id
, p_resource_id => p_resource_id
, p_last_run_date => NULL
, p_changed => 'Y'
, p_old_org_id => r_org.organization_id );
UPDATE_RESOURCE_PROFILE_REC( p_resource_id => p_resource_id
, p_organization_id => l_profile_org_id
, p_category_set_id => l_profile_category_set_id
, p_category_id => l_profile_category_id );
-> push any inserted items to resource (updates are pushed
in main concurrent procedure in non-resource-specific call)
*/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( p_resource_id
, g_table_name
, 'Pushing inserted records for'||fnd_global.local_chr(10)||
'organization_id = ' || l_profile_org_id||fnd_global.local_chr(10)||
'category_set_id = ' || l_profile_category_set_id||fnd_global.local_chr(10)||
'category_id = ' || l_profile_category_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
INSERT_ACC_REC_MARKDIRTY( p_organization_id => l_profile_org_id
, p_category_set_id => l_profile_category_set_id
, p_category_id => l_profile_category_id
, p_resource_id => p_resource_id
, p_last_run_date => p_last_run_date
, p_changed => 'N'
, p_old_org_id => r_org.organization_id );
select LAST_RUN_DATE
from JTM_CON_REQUEST_DATA
where package_name = 'CSL_MTL_SYSTEM_ITEMS_ACC_PKG'
AND procedure_name = 'CON_REQUEST_MTL_SYSTEM_ITEMS';
SELECT res.resource_id
, usr.user_id
, usrresp.responsibility_id
, usrresp.responsibility_application_id
FROM asg_pub pub
, asg_pub_responsibility pubresp
, fnd_user_resp_groups usrresp
, fnd_user usr
, jtf_rs_resource_extns res
, asg_user au
WHERE res.resource_id = au.resource_id --b_resource_id
AND pub.name = 'SERVICEL'
AND pub.enabled='Y'
AND pub.status='Y'
AND pub.pub_id = pubresp.pub_id
AND pubresp.responsibility_id = usrresp.responsibility_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usrresp.start_date,sysdate))
AND TRUNC(NVL(usrresp.end_date,sysdate))
AND usrresp.user_id = usr.user_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usr.start_date,sysdate))
AND TRUNC(NVL(usr.end_date,sysdate))
AND usr.user_id = res.user_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active,sysdate))
AND TRUNC(NVL(res.end_date_active,sysdate));
/*** Push updated system item records to resources ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( 0
, g_table_name
, 'Pushing updated records'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
UPDATE_ACC_REC_MARKDIRTY( p_last_run_date => r_LastRundate.last_run_date );
/*Update the last run date*/
UPDATE JTM_CON_REQUEST_DATA
SET LAST_RUN_DATE = l_current_run_date
WHERE package_name = 'CSL_MTL_SYSTEM_ITEMS_ACC_PKG'
AND procedure_name = 'CON_REQUEST_MTL_SYSTEM_ITEMS';