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 JTM_MTL_SYSTEM_ITEMS_ACC

Line 5: g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_MTL_SYSTEM_ITEMS_ACC';

1: PACKAGE BODY CSL_MTL_SYSTEM_ITEMS_ACC_PKG AS
2: /* $Header: cslsiacb.pls 120.0 2005/05/24 18:40:25 appldev noship $ */
3:
4: /*** Globals ***/
5: g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_MTL_SYSTEM_ITEMS_ACC';
6: g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7: JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_SYSTEM_ITEMS_VL');
8:
9: --Bug 3746689

Line 74: UPDATE jtm_mtl_system_items_acc

70:
71: BEGIN
72:
73: IF ( p_organization_id <> p_old_org_id ) THEN
74: UPDATE jtm_mtl_system_items_acc
75: SET counter = counter + 1
76: , last_update_date = SYSDATE
77: , last_updated_by = 1
78: WHERE resource_id = p_resource_id

Line 104: FROM jtm_mtl_system_items_acc

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 )
106: AND msi.material_billable_flag = cbtc.billing_type (+)
107: AND cbtc.billing_category IN ('E', 'L')
108: AND organization_id = p_organization_id;

Line 123: INSERT INTO jtm_mtl_system_items_acc(

119: );
120: END IF;
121:
122: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
123: INSERT INTO jtm_mtl_system_items_acc(
124: access_id, last_update_date, last_updated_by, creation_date,
125: created_by , counter, resource_id, inventory_item_id, organization_id)
126: VALUES (
127: l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,

Line 521: DELETE JTM_MTL_SYSTEM_ITEMS_ACC

517: );
518: END IF;
519:
520: /*Do the actual delete*/
521: DELETE JTM_MTL_SYSTEM_ITEMS_ACC
522: WHERE RESOURCE_ID = p_resource_id;
523:
524: DELETE CSL_MTL_SECONDARY_LOCATORS_ACC
525: WHERE RESOURCE_ID = p_resource_id;

Line 643: l_stmt := 'INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC (';

639:
640: /*Block insert every item from given org not yet in acc table - Material*/
641:
642: --Bug 3929942 - Added Hints and use bind variables
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)*/ ';

Line 657: l_stmt := l_stmt || ' SELECT /+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)/ INVENTORY_ITEM_ID, ORGANIZATION_ID';

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 ) ';
656: l_stmt := l_stmt || ' NOT IN (';
657: l_stmt := l_stmt || ' SELECT /*+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)*/ INVENTORY_ITEM_ID, ORGANIZATION_ID';
658: l_stmt := l_stmt || ' FROM JTM_MTL_SYSTEM_ITEMS_ACC acc ';
659: l_stmt := l_stmt || ' WHERE RESOURCE_ID = :2 )';
660:
661: IF (l_profile_category_id IS NOT NULL) THEN

Line 658: l_stmt := l_stmt || ' FROM JTM_MTL_SYSTEM_ITEMS_ACC acc ';

654: l_stmt := l_stmt || ' AND NVL(cbtc.billing_category, ''M'') = ''M''';
655: l_stmt := l_stmt || ' AND ( INVENTORY_ITEM_ID, ORGANIZATION_ID ) ';
656: l_stmt := l_stmt || ' NOT IN (';
657: l_stmt := l_stmt || ' SELECT /*+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)*/ INVENTORY_ITEM_ID, ORGANIZATION_ID';
658: l_stmt := l_stmt || ' FROM JTM_MTL_SYSTEM_ITEMS_ACC acc ';
659: l_stmt := l_stmt || ' WHERE RESOURCE_ID = :2 )';
660:
661: IF (l_profile_category_id IS NOT NULL) THEN
662: l_stmt1 := 'itemcat.category_id = ' || l_profile_category_id;

Line 696: INSERT INTO JTM_MTL_SYSTEM_ITEMS_ACC(access_id, last_update_date, last_updated_by,

692:
693: /*Block insert every item from given org not yet in acc table - Expense and Labor*/
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

Line 706: SELECT /+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)/ inventory_item_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 )
705: NOT IN (
706: SELECT /*+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)*/ inventory_item_id,
707: organization_id
708: FROM jtm_mtl_system_items_acc acc
709: WHERE resource_id = p_resource_id );
710:

Line 708: FROM jtm_mtl_system_items_acc acc

704: AND ( inventory_item_id, organization_id )
705: NOT IN (
706: SELECT /*+ index (acc JTM_MTL_SYSTEM_ITEMS_ACC_U1)*/ inventory_item_id,
707: organization_id
708: FROM jtm_mtl_system_items_acc acc
709: WHERE resource_id = p_resource_id );
710:
711: /*REDUCE ROLLBACK SEGMENTS*/
712: COMMIT;

Line 805: FROM JTM_MTL_SYSTEM_ITEMS_ACC

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 )
807: AND ORGANIZATION_ID = b_organization_id
808: AND material_billable_flag = billing_type (+)
809: AND NVL(cbtc.billing_category, 'M') = 'M'

Line 823: FROM JTM_MTL_SYSTEM_ITEMS_ACC

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 )
825: AND ORGANIZATION_ID = b_organization_id
826: AND material_billable_flag = billing_type (+)
827: AND NVL(cbtc.billing_category, 'M') = 'M'

Line 846: FROM JTM_MTL_SYSTEM_ITEMS_ACC acc

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
848: AND msi.INVENTORY_ITEM_ID = acc.INVENTORY_ITEM_ID
849: AND msi.ORGANIZATION_ID = acc.ORGANIZATION_ID)
850: AND NVL(MATERIAL_BILLABLE_FLAG, 'M') = 'M'

Line 870: FROM JTM_MTL_SYSTEM_ITEMS_ACC

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 )
872: AND ORGANIZATION_ID = b_organization_id
873: AND material_billable_flag = billing_type (+)
874: AND NVL(cbtc.billing_category, 'M') = 'M'

Line 911: l_stmt := 'UPDATE jtm_mtl_system_items_acc';

907: END IF;
908:
909: --Bug 3724165
910: --Bug 3929942 - Use Bind variables
911: l_stmt := 'UPDATE jtm_mtl_system_items_acc';
912: l_stmt := l_stmt || ' SET counter = counter + 1';
913: l_stmt := l_stmt || ' , last_update_date = SYSDATE';
914: l_stmt := l_stmt || ' , last_updated_by = 1';
915: l_stmt := l_stmt || ' WHERE resource_id = :1 ';

Line 990: INSERT INTO jtm_mtl_system_items_acc(

986: );
987: END IF;
988:
989: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
990: INSERT INTO jtm_mtl_system_items_acc(
991: access_id, last_update_date, last_updated_by, creation_date,
992: created_by, counter, resource_id, inventory_item_id, organization_id )
993: VALUES (
994: l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,

Line 1041: INSERT INTO jtm_mtl_system_items_acc(

1037: );
1038: END IF;
1039:
1040: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
1041: INSERT INTO jtm_mtl_system_items_acc(
1042: access_id, last_update_date, last_updated_by, creation_date,
1043: created_by, counter, resource_id, inventory_item_id, organization_id )
1044: VALUES (
1045: l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,

Line 1092: INSERT INTO jtm_mtl_system_items_acc(

1088: );
1089: END IF;
1090:
1091: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
1092: INSERT INTO jtm_mtl_system_items_acc(
1093: access_id, last_update_date, last_updated_by, creation_date,
1094: created_by, counter, resource_id, inventory_item_id, organization_id )
1095: VALUES (
1096: l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,

Line 1143: INSERT INTO jtm_mtl_system_items_acc(

1139: );
1140: END IF;
1141:
1142: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
1143: INSERT INTO jtm_mtl_system_items_acc(
1144: access_id, last_update_date, last_updated_by, creation_date,
1145: created_by, counter, resource_id, inventory_item_id, organization_id )
1146: VALUES (
1147: l_tab_access_id(i), SYSDATE, 1, SYSDATE, 1, 1, p_resource_id,

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 1428: UPDATE jtm_mtl_system_items_acc

1424: /* Delete all records for org/category */
1425: --Bug 3724165
1426: -- Expense and Labor Items
1427: IF ( p_organization_id <> p_profile_org_id ) THEN
1428: UPDATE jtm_mtl_system_items_acc
1429: SET counter = counter - 1,
1430: last_update_date = SYSDATE
1431: WHERE resource_id = p_resource_id
1432: AND (inventory_item_id, organization_id) IN

Line 1442: l_stmt := 'UPDATE JTM_MTL_SYSTEM_ITEMS_ACC';

1438: END IF;
1439:
1440: --Bug 3724165
1441: -- Material Items
1442: l_stmt := 'UPDATE JTM_MTL_SYSTEM_ITEMS_ACC';
1443: l_stmt := l_stmt || ' SET COUNTER = COUNTER - 1';
1444: l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
1445: l_stmt := l_stmt || ' WHERE RESOURCE_ID = :1';
1446:

Line 1494: FROM JTM_MTL_SYSTEM_ITEMS_ACC acc,

1490: --Bug 3746689
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

Line 1555: DELETE JTM_MTL_SYSTEM_ITEMS_ACC

1551:
1552:
1553: /*To avoid a mismatch only delete records which are marked dirty*/
1554: FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
1555: DELETE JTM_MTL_SYSTEM_ITEMS_ACC
1556: WHERE ACCESS_ID = l_tab_access_id(i);
1557: END IF;
1558:
1559: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN