[Home] [Help]
189: )
190: IS
191:
192: CURSOR els_history(l_org_id NUMBER,l_purge_date VARCHAR2) IS SELECT els_data_id
193: FROM wms_els_individual_tasks_b
194: WHERE history_flag = 1
195: AND organization_id = l_org_id
196: AND archive_date <= NVL (TO_DATE(l_purge_date,'YYYY/MM/DD HH24:MI:SS') ,SYSDATE);
197:
248: -- been marked as hsitory
249:
250: FORALL j IN l_els_id_tab.first..l_els_id_tab.last
251:
252: DELETE wms_els_individual_tasks_b
253: WHERE els_data_id = l_els_id_tab(j);
254:
255: l_records_deleted :=SQL%ROWCOUNT;
256:
254:
255: l_records_deleted :=SQL%ROWCOUNT;
256:
257: IF g_debug=1 THEN
258: debug('Number of records deleted from wms_els_individual_tasks_b table '|| l_records_deleted,
259: 'PURGE_LMS_SETUP_HISTORY'
260: );
261: END IF;
262:
574: , ATTRIBUTE12
575: , ATTRIBUTE13
576: , ATTRIBUTE14
577: , ATTRIBUTE15
578: FROM wms_els_individual_tasks_b
579: WHERE organization_id= l_org_id
580: AND nvl(HISTORY_FLAG, -999) <> 1;
581:
582: /* The following cursor has been added to fetch the records from TL table
615: SELECT WMS_ELS_INDIVIDUAL_TASKS_S.NEXTVAL
616: INTO l_new_els_data_id
617: FROM dual;
618:
619: INSERT INTO wms_els_individual_tasks_b (
620: ELS_DATA_ID
621: , ORGANIZATION_ID
622: , SEQUENCE_NUMBER
623: , ANALYSIS_ID
750:
751: /* End of fix for bug # 5520074 */
752:
753: --update the expected value fields with the actual values.
754: UPDATE wms_els_individual_tasks_b
755: SET
756: Expected_Travel_Time = NVL(Actual_Travel_time,Expected_Travel_Time),
757: Expected_Txn_Time = NVL(Actual_Txn_Time,Expected_Txn_Time),
758: Expected_Idle_Time = NVL(Actual_Idle_Time,Expected_Idle_Time),
860: ) IS
861:
862:
863: CURSOR C_ELS_DATA_ID (l_organization_id NUMBER) IS
864: SELECT els_data_id FROM WMS_ELS_INDIVIDUAL_TASKS_B WHERE
865: organization_id=l_organization_id
866: and history_flag IS NULL;
867:
868: -- Ignore history rows
964: FORALL i IN l_els_data_id_tab.first .. l_els_data_id_tab.last
965: --Bulk update the els data table with actual timings.
966: --Bulk update is used here for performance enhancement resons.
967:
968: UPDATE wms_els_individual_tasks_b SET
969: actual_travel_time=(
970: SELECT AVG(travel_time) FROM WMS_ELS_TRX_SRC
971: WHERE els_data_id=l_els_data_id_tab(i)
972: AND NVL(Transaction_Date,SYSDATE) >= NVL(l_date,SYSDATE)
1427:
1428:
1429: BEGIN
1430: select round(MAX(sequence_number),-1) into x_seq_num_ind_and_sys_directed
1431: from WMS_ELS_INDIVIDUAL_TASKS_B
1432: where group_id= 3
1433: AND history_flag IS null
1434: AND organization_id = p_org_id;-- individual group
1435: EXCEPTION
1438: END;
1439:
1440: BEGIN
1441: select round(MAX(sequence_number),-1) into x_seq_num_man_and_sys_directed
1442: from WMS_ELS_INDIVIDUAL_TASKS_B
1443: where group_id= 2
1444: AND history_flag IS null
1445: AND organization_id = p_org_id; -- manual group
1446: EXCEPTION
1449: END;
1450:
1451: BEGIN
1452: select round(MAX(sequence_number),-1) into x_seq_num_man_and_usr_directed
1453: from WMS_ELS_INDIVIDUAL_TASKS_B
1454: where group_id= 1
1455: AND history_flag IS null
1456: AND organization_id = p_org_id; -- manual group
1457: EXCEPTION
1552: END IF;
1553:
1554: -- Proceed if all required parameters are passed.
1555: -- get the max of the sequence numbers (as per HLD)in the
1556: -- setup table(WMS_ELS_INDIVIDUAL_TASKS_B)
1557: -- for both the Individual and the manual group.
1558:
1559: IF (P_ORG_ID IS NOT NULL) THEN
1560:
1664: BEGIN
1665: -- dbms_output.put_line(l_els_trx_src_id || ' els_trx_src_id value');
1666: if( l_els_trx_src_id <> 0 ) then
1667:
1668: INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B
1669: (
1670: els_data_id
1671: , organization_id
1672: , sequence_number
1809: , b.last_update_login
1810: , b.created_by
1811: , b.creation_date
1812: , b.last_update_date
1813: FROM WMS_ELS_INDIVIDUAL_TASKS_B b,
1814: FND_LANGUAGES L
1815: WHERE els_data_id NOT IN (
1816: SELECT DISTINCT els_data_id
1817: FROM wms_els_individual_tasks_tl
1823: --X_NUM_LINES_INSERTED_TASKS := SQL%ROWCOUNT;
1824:
1825:
1826: IF g_debug=1 THEN
1827: debug('Num lines inserted into WMS_ELS_INDIVIDUAL_TASKS_B '||X_NUM_LINES_INSERTED_TASKS ,'STANDARDIZE_LINES');
1828: debug('Before inserting into grouped tasks table','STANDARDIZE_LINES');
1829: END IF;
1830:
1831:
2071: BEGIN
2072: -- for manual and user directed tasks
2073:
2074: EXECUTE IMMEDIATE
2075: ' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
2076: || ' ( '
2077: || ' els_data_id '
2078: || ' , organization_id '
2079: || ' , sequence_number '
2187: -- for manual and system directed tasks
2188: BEGIN
2189:
2190: EXECUTE IMMEDIATE
2191: ' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
2192: || ' ( '
2193: || ' els_data_id '
2194: || ' , organization_id '
2195: || ' , sequence_number '
2305:
2306: BEGIN
2307:
2308: EXECUTE IMMEDIATE
2309: ' INSERT INTO WMS_ELS_INDIVIDUAL_TASKS_B '
2310: || ' ( '
2311: || ' els_data_id '
2312: || ' , organization_id '
2313: || ' , sequence_number '
2457: , b.last_update_login
2458: , b.created_by
2459: , b.creation_date
2460: , b.last_update_date
2461: FROM WMS_ELS_INDIVIDUAL_TASKS_B b,
2462: FND_LANGUAGES L
2463: WHERE els_data_id NOT IN (
2464: SELECT DISTINCT els_data_id
2465: FROM wms_els_individual_tasks_tl
2484: RETURN;
2485:
2486: END;
2487:
2488: -- Now when the insertion to WMS_ELS_INDIVIDUAL_TASKS_B and
2489: -- WMS_ELS_INDIVIDUAL_TASKS_TL table is successfull now we can proceed
2490: -- to inserting lines in the WMS_ELS_GROUPED_TASKS_B and WMS_ELS_GROUPED_TASKS_TL table
2491:
2492: l_num_lines_inserted :=0;--reinitialize