[Home] [Help]
93: );
94: END IF;
95:
96: /*Block insert every item from given org not yet in acc table*/
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
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';
652: l_stmt := l_stmt || ' WHERE organization_id = :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 (+)
796: -- Both category and cat set are null
797: CURSOR c_items (b_resource_id NUMBER, b_organization_id NUMBER,
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
814: -- Category is not null and Cat set is null
815: CURSOR c_items_Cat (b_resource_id NUMBER, b_organization_id NUMBER,
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
837: -- Category is null and Cat Set is not null
838: CURSOR c_items_Cat_Set (b_resource_id NUMBER, b_organization_id NUMBER,
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
861: -- Both Category and Category set are null
862: CURSOR c_items_Cat_Set_Cat (b_resource_id NUMBER, b_organization_id NUMBER,
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