eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSL_MTL_SYSTEM_ITEMS_ACC_PKG dependencies on MTL_SYSTEM_ITEMS_B

Line 13: g_table_name CONSTANT VARCHAR2(30) := 'MTL_SYSTEM_ITEMS_B';

9: --Bug 3746689
10: g_explab_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
11: JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_MTL_EXPENSE_LABOR_ITEM');
12:
13: g_table_name CONSTANT VARCHAR2(30) := 'MTL_SYSTEM_ITEMS_B';
14: g_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
15: g_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
16: g_debug_level NUMBER; -- debug level
17:

Line 63: TYPE item_Tab IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;

59:
60: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
61: l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
62:
63: TYPE item_Tab IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
64: TYPE org_Tab IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
65: items item_Tab;
66: organizations org_Tab;
67:

Line 64: TYPE org_Tab IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;

60: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
61: l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
62:
63: TYPE item_Tab IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
64: TYPE org_Tab IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
65: items item_Tab;
66: organizations org_Tab;
67:
68: l_dummy BOOLEAN;

Line 81: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc

77: , last_updated_by = 1
78: WHERE resource_id = p_resource_id
79: AND (inventory_item_id, organization_id)
80: IN (SELECT inventory_item_id, organization_id
81: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
82: WHERE organization_id = p_organization_id
83: and msi.material_billable_flag = cbtc.billing_type (+)
84: AND cbtc.billing_category IN ('E','L'));
85: END IF;

Line 101: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc

97: SELECT jtm_acc_table_s.NEXTVAL, inventory_item_id, organization_id,
98: p_resource_id
99: BULK COLLECT INTO
100: l_tab_access_id, items, organizations, l_tab_resource_id
101: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
102: WHERE ( inventory_item_id, organization_id ) NOT IN (
103: SELECT inventory_item_id, organization_id
104: FROM jtm_mtl_system_items_acc
105: WHERE resource_id = p_resource_id )

Line 177: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc

173: --Bug 3908277 - Static Query converted into Cursor.
174: CURSOR c_billCat(b_inventory_item_id NUMBER, b_organization_id NUMBER)
175: IS
176: SELECT billing_category
177: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
178: WHERE msi.material_billable_flag = cbtc.billing_type (+)
179: AND inventory_item_id = p_inventory_item_id
180: AND organization_id = p_organization_id;
181:

Line 264: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc

260: --Bug 3908277 - Static Query converted into Cursor.
261: CURSOR c_billCat(b_inventory_item_id NUMBER, b_organization_id NUMBER)
262: IS
263: SELECT billing_category
264: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
265: WHERE msi.material_billable_flag = cbtc.billing_type (+)
266: AND inventory_item_id = p_inventory_item_id
267: AND organization_id = p_organization_id;
268:

Line 647: l_stmt := l_stmt || ' SELECT /+ index (msi MTL_SYSTEM_ITEMS_B_N4)/ ';

643: l_stmt := 'INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC (';
644: l_stmt := l_stmt || ' access_id, last_update_date, last_updated_by, ';
645: l_stmt := l_stmt || ' creation_date, created_by, counter, resource_id, ';
646: l_stmt := l_stmt || ' inventory_item_id, organization_id )';
647: l_stmt := l_stmt || ' SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_N4)*/ ';
648: l_stmt := l_stmt || ' jtm_acc_table_s.NEXTVAL, SYSDATE, 1, ';
649: l_stmt := l_stmt || ' SYSDATE, 1, 1,'||p_resource_id ||', ';
650: l_stmt := l_stmt || ' inventory_item_id, organization_id ';
651: l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';

Line 651: l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';

647: l_stmt := l_stmt || ' SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_N4)*/ ';
648: l_stmt := l_stmt || ' jtm_acc_table_s.NEXTVAL, SYSDATE, 1, ';
649: l_stmt := l_stmt || ' SYSDATE, 1, 1,'||p_resource_id ||', ';
650: l_stmt := l_stmt || ' inventory_item_id, organization_id ';
651: l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';
652: l_stmt := l_stmt || ' WHERE organization_id = :1 ';
653: l_stmt := l_stmt || ' AND msi.material_billable_flag = cbtc.billing_type (+) ';
654: l_stmt := l_stmt || ' AND NVL(cbtc.billing_category, ''M'') = ''M''';
655: l_stmt := l_stmt || ' AND ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) ';

Line 698: SELECT /+ index (msi MTL_SYSTEM_ITEMS_B_N4)/ jtm_acc_table_s.NEXTVAL, SYSDATE, 1,

694:
695: --Bug 3929942 - Added Hints
696: INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC(access_id, last_update_date, last_updated_by,
697: creation_date, created_by, counter, resource_id,inventory_item_id, organization_id )
698: SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_N4)*/ jtm_acc_table_s.NEXTVAL, SYSDATE, 1,
699: SYSDATE, 1, 1, p_resource_id, inventory_item_id, organization_id
700: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
701: WHERE organization_id = l_profile_org_id
702: AND msi.material_billable_flag = cbtc.billing_type (+)

Line 700: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc

696: INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC(access_id, last_update_date, last_updated_by,
697: creation_date, created_by, counter, resource_id,inventory_item_id, organization_id )
698: SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_N4)*/ jtm_acc_table_s.NEXTVAL, SYSDATE, 1,
699: SYSDATE, 1, 1, p_resource_id, inventory_item_id, organization_id
700: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
701: WHERE organization_id = l_profile_org_id
702: AND msi.material_billable_flag = cbtc.billing_type (+)
703: AND cbtc.billing_category IN ('E','L')
704: AND ( inventory_item_id, organization_id )

Line 771: TYPE item_Tab IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;

767: --by the procedure INSERT_ACC_REC_MARKDIRTY_EXP
768: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
769: l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
770:
771: TYPE item_Tab IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
772: TYPE org_Tab IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
773:
774: items item_Tab;
775: organizations org_Tab;

Line 772: TYPE org_Tab IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;

768: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
769: l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
770:
771: TYPE item_Tab IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
772: TYPE org_Tab IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
773:
774: items item_Tab;
775: organizations org_Tab;
776:

Line 802: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc

798: b_changed VARCHAR2, b_last_run_date DATE)
799: IS
800: SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
801: BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
802: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
803: WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
804: SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
805: FROM JTM_MTL_SYSTEM_ITEMS_ACC
806: WHERE RESOURCE_ID = b_resource_id )

Line 820: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc

816: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER)
817: IS
818: SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
819: BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
820: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
821: WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
822: SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
823: FROM JTM_MTL_SYSTEM_ITEMS_ACC
824: WHERE RESOURCE_ID = b_resource_id )

Line 843: FROM MTL_SYSTEM_ITEMS_B msi

839: b_changed VARCHAR2, b_last_run_date DATE, b_category_set_id NUMBER)
840: IS
841: SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID,
842: p_resource_id, material_billable_flag
843: FROM MTL_SYSTEM_ITEMS_B msi
844: WHERE NOT EXISTS (
845: SELECT 1
846: FROM JTM_MTL_SYSTEM_ITEMS_ACC acc
847: WHERE RESOURCE_ID = b_resource_id

Line 867: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc

863: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER, b_category_set_id NUMBER)
864: IS
865: SELECT JTM_ACC_TABLE_S.NEXTVAL, INVENTORY_ITEM_ID, ORGANIZATION_ID, p_resource_id, billing_category
866: BULK COLLECT INTO l_tab_access_id, items, organizations, l_tab_resource_id, billCat
867: FROM MTL_SYSTEM_ITEMS_B msi, CS_BILLING_TYPE_CATEGORIES cbtc
868: WHERE ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) NOT IN (
869: SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
870: FROM JTM_MTL_SYSTEM_ITEMS_ACC
871: WHERE RESOURCE_ID = b_resource_id )

Line 922: l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';

918: -- Check material billable flag
919:
920: l_stmt := l_stmt || ' AND (inventory_item_id, organization_id) IN ';
921: l_stmt := l_stmt || ' (SELECT inventory_item_id, organization_id ';
922: l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';
923: l_stmt := l_stmt || ' WHERE organization_id = :2 ';
924: l_stmt := l_stmt || ' and msi.material_billable_flag = cbtc.billing_type (+) ';
925: l_stmt := l_stmt || ' AND NVL(cbtc.billing_category, ''M'') = ''M'')';
926:

Line 1199: SELECT /+ INDEX (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1) index (msi MTL_SYSTEM_ITEMS_B_U1) /

1195: PROCEDURE UPDATE_ACC_REC_MARKDIRTY( p_last_run_date IN DATE )
1196: IS
1197: --Bug 3929942 - Modified the query to remove UNION
1198: CURSOR c_changed( b_last_date DATE ) IS
1199: SELECT /*+ INDEX (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1) index (msi MTL_SYSTEM_ITEMS_B_U1) */
1200: acc.ACCESS_ID, acc.RESOURCE_ID, cbtc.BILLING_CATEGORY
1201: FROM JTM_MTL_SYSTEM_ITEMS_ACC acc, MTL_SYSTEM_ITEMS_B msi
1202: , CS_BILLING_TYPE_CATEGORIES cbtc
1203: WHERE msi.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID

Line 1201: FROM JTM_MTL_SYSTEM_ITEMS_ACC acc, MTL_SYSTEM_ITEMS_B msi

1197: --Bug 3929942 - Modified the query to remove UNION
1198: CURSOR c_changed( b_last_date DATE ) IS
1199: SELECT /*+ INDEX (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1) index (msi MTL_SYSTEM_ITEMS_B_U1) */
1200: acc.ACCESS_ID, acc.RESOURCE_ID, cbtc.BILLING_CATEGORY
1201: FROM JTM_MTL_SYSTEM_ITEMS_ACC acc, MTL_SYSTEM_ITEMS_B msi
1202: , CS_BILLING_TYPE_CATEGORIES cbtc
1203: WHERE msi.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
1204: AND msi.ORGANIZATION_ID = acc.ORGANIZATION_ID
1205: AND msi.material_billable_flag = cbtc.billing_type (+)

Line 1249: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */

1245: );
1246: END IF;
1247:
1248: --Bug 3929942
1249: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
1250: /* , MTL_SYSTEM_ITEMS_TL which were custom created */
1251: SELECT MAX(LAST_UPDATE_DATE) into l_max_last_update_date_b
1252: FROM MTL_SYSTEM_ITEMS_B;
1253: IF( l_max_last_update_date_b < p_last_run_date) THEN

Line 1252: FROM MTL_SYSTEM_ITEMS_B;

1248: --Bug 3929942
1249: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
1250: /* , MTL_SYSTEM_ITEMS_TL which were custom created */
1251: SELECT MAX(LAST_UPDATE_DATE) into l_max_last_update_date_b
1252: FROM MTL_SYSTEM_ITEMS_B;
1253: IF( l_max_last_update_date_b < p_last_run_date) THEN
1254: SELECT MAX(LAST_UPDATE_DATE) into l_max_last_update_date_tl
1255: FROM MTL_SYSTEM_ITEMS_TL;
1256: IF(l_max_last_update_date_tl < p_last_run_date) THEN

Line 1434: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc

1430: last_update_date = SYSDATE
1431: WHERE resource_id = p_resource_id
1432: AND (inventory_item_id, organization_id) IN
1433: (SELECT inventory_item_id, organization_id
1434: FROM mtl_system_items_b msi, cs_billing_type_categories cbtc
1435: WHERE organization_id = p_organization_id
1436: and msi.material_billable_flag = cbtc.billing_type (+)
1437: AND cbtc.billing_category IN ('E','L'));
1438: END IF;

Line 1451: l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';

1447: IF ((p_category_id IS NULL) AND (p_category_set_id IS NULL)) THEN
1448: -- Check material billable flag
1449: l_stmt := l_stmt || ' AND (inventory_item_id, organization_id) IN ';
1450: l_stmt := l_stmt || ' (SELECT inventory_item_id, organization_id ';
1451: l_stmt := l_stmt || ' FROM mtl_system_items_b msi, cs_billing_type_categories cbtc';
1452: l_stmt := l_stmt || ' WHERE organization_id = :2';
1453: l_stmt := l_stmt || ' and msi.material_billable_flag = cbtc.billing_type (+) ';
1454: l_stmt := l_stmt || ' AND NVL(cbtc.billing_category, ''M'') = ''M'')';
1455: ELSE -- category would ensure material items

Line 1495: MTL_SYSTEM_ITEMS_B msi,

1491: --Bug 3908277
1492: SELECT ACCESS_ID, p_resource_id, billing_category
1493: BULK COLLECT INTO l_tab_access_id, l_tab_resource_id, billCat
1494: FROM JTM_MTL_SYSTEM_ITEMS_ACC acc,
1495: MTL_SYSTEM_ITEMS_B msi,
1496: CS_BILLING_TYPE_CATEGORIES cbtc
1497: WHERE msi.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
1498: AND msi.ORGANIZATION_ID = acc.ORGANIZATION_ID
1499: AND msi.material_billable_flag = cbtc.billing_type (+)