[Home] [Help]
31: -- This is the main procedure that will be called while running Matadata Import Concurrent --
32: -- Program, to process Functions and Function Parameters. --
33: -- Parameters: --
34: -- IN --
35: -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
36: -- processed in a batch. --
37: -- OUT --
38: -- x_return_status: Return status. Can be S or U (Unexpected Error). --
39: -- x_return_msg: Stores the error message, if unexpected error occurs. --
66: -- This procedure will be called at end by Matadata Import Concurrent Program, --
67: -- to delete processed rows from Functions and Function Parameters interface tables. --
68: -- Parameters: --
69: -- IN --
70: -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
71: -- belongs to a particular batch. --
72: -- OUT --
73: -- x_return_status: Return status. Can be S or U (Unexpected Error). --
74: -- x_return_msg: Stores the error message, if unexpected error occurs. --
80: l_proc_name VARCHAR2(30) := 'delete_processed_functions';
81: BEGIN
82: write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
83: x_return_status := fnd_api.G_RET_STS_SUCCESS;
84: DELETE FROM ego_functions_interface WHERE (p_set_process_id IS NULL OR set_process_id=p_set_process_id)
85: AND process_status=G_SUCCESS_RECORD;
86: DELETE FROM ego_func_params_interface WHERE (p_set_process_id IS NULL OR set_process_id=p_set_process_id)
87: AND process_status=G_SUCCESS_RECORD;
88: write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
98: -- a function and also converts SYNC transaction to either CREATE or UPDATE, --
99: -- if the validation succeeds. --
100: -- Parameters: --
101: -- IN OUT --
102: -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
103: ---------------------------------------------------------------------------------------------
104: PROCEDURE construct_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
105: invalid_function_id NUMBER(1);
106: invalid_internal_name NUMBER(1);
100: -- Parameters: --
101: -- IN OUT --
102: -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
103: ---------------------------------------------------------------------------------------------
104: PROCEDURE construct_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
105: invalid_function_id NUMBER(1);
106: invalid_internal_name NUMBER(1);
107: l_proc_name VARCHAR2(30) := 'construct_function';
108: BEGIN
224: -- This procedure will perform the remaining validations (excluding the validations done --
225: -- on key columns in construct_function) based on the transaction type. --
226: -- Parameters: --
227: -- IN OUT --
228: -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
229: ---------------------------------------------------------------------------------------------
230: PROCEDURE validate_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
231: temporary_record ego_functions_b%ROWTYPE;
232: temporary_record_tl ego_functions_tl%ROWTYPE;
226: -- Parameters: --
227: -- IN OUT --
228: -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
229: ---------------------------------------------------------------------------------------------
230: PROCEDURE validate_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
231: temporary_record ego_functions_b%ROWTYPE;
232: temporary_record_tl ego_functions_tl%ROWTYPE;
233: valid_function_type NUMBER(1);
234: duplicate_internal_name NUMBER(1);
461: -- This procedure will update the base table, with the data in func_header_rec, only if --
462: -- there are no validation errors (process_status<>3), based on transaction type. --
463: -- Parameters: --
464: -- IN OUT --
465: -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
466: ---------------------------------------------------------------------------------------------
467: PROCEDURE transact_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
468: l_proc_name VARCHAR2(30) := 'transact_function';
469: BEGIN
463: -- Parameters: --
464: -- IN OUT --
465: -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
466: ---------------------------------------------------------------------------------------------
467: PROCEDURE transact_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
468: l_proc_name VARCHAR2(30) := 'transact_function';
469: BEGIN
470: write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
471: /* Insert into base table, if a row does not have any errors. */
559: -- This will be invoked in Concurrent Request flow. --
560: -- This procedure will update the interface table back after processing the records. --
561: -- Parameters: --
562: -- IN OUT --
563: -- ego_func_tbl_values - Represents a table of type ego_functions_interface%ROWTYPE. --
564: ---------------------------------------------------------------------------------------------
565: /* Bug 9701271. Changing the implementation, as reference like table(bulk_index).field in FORALL statement
566: are not supported, before 11g release.*/
567: PROCEDURE update_intfc_functions(ego_func_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_function_tbl_type) IS
577: END LOOP;
578:
579: /* Update the interface table back. */
580: FORALL i IN 1 .. ego_func_tbl_values.COUNT
581: UPDATE ego_functions_interface
582: SET ROW = ego_func_tbl_values(i)
583: WHERE ( transaction_id = transaction_id_table(i) );
584: /* Commit after updating the interface table back. */
585: COMMIT;
592: -- This procedure will process all the functions one by one. Technically, it will call the --
593: -- previous three functions. --
594: -- Parameters: --
595: -- IN OUT --
596: -- ego_func_tbl_values - Represents a table of type ego_functions_interface%ROWTYPE. --
597: -- p_commit - Indicates whether to commit the work or not. This parameter --
598: -- has significance only in public API flow. --
599: ---------------------------------------------------------------------------------------------
600: PROCEDURE process_functions(ego_func_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_function_tbl_type,
630: -- This procedure will intialize functions interface table with by updating the --
631: -- "WHO" columns, transaction_id and convering the transction_type to upper case. --
632: -- Parameters: --
633: -- IN --
634: -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
635: -- belongs to a particular batch. --
636: ---------------------------------------------------------------------------------------------
637: PROCEDURE initialize_functions(p_set_process_id IN NUMBER)
638: IS
638: IS
639: l_proc_name VARCHAR2(30) := 'initialize_functions';
640: BEGIN
641: write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
642: UPDATE ego_functions_interface
643: SET transaction_id = mtl_system_items_interface_s.nextval,
644: transaction_type = Upper(transaction_type),
645: last_update_date = sysdate,
646: last_updated_by = G_USER_ID,
655:
656: ---------------------------------------------------------------------------------------------
657: -- Procedure Name: process_functions_conc_flow --
658: -- This will be invoked in Concurrent Request flow. --
659: -- This procedure will read the data in chunks from ego_functions_interface table, --
660: -- processes them by calling process_functions() and then updates interface table --
661: -- back by calling update_intfc_functions(). --
662: -- Parameters: --
663: -- IN --
660: -- processes them by calling process_functions() and then updates interface table --
661: -- back by calling update_intfc_functions(). --
662: -- Parameters: --
663: -- IN --
664: -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
665: -- processed in a batch. --
666: ---------------------------------------------------------------------------------------------
667: PROCEDURE process_functions_conc_flow(p_set_process_id IN NUMBER)
668: IS
673: write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
674:
675: /* Bug 9671972. Return if there are no rows to process. */
676: SELECT Count(*) INTO record_count
677: FROM ego_functions_interface
678: WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
679: AND process_status = G_PROCESS_RECORD );
680:
681: IF (record_count=0) THEN
708: -- This will be invoked in Concurrent Request flow. --
709: -- This procedure will do bulk validations. --
710: -- Parameters: --
711: -- IN --
712: -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
713: -- processed in a batch. --
714: ----------------------------------------------------------------------------------------------
715: PROCEDURE bulk_validate_functions(p_set_process_id IN NUMBER)
716: IS
759: G_REQUEST_ID,
760: G_PROGRAM_APPLICATION_ID,
761: G_PROGRAM_ID,
762: SYSDATE
763: FROM ego_functions_interface
764: WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
765: OR transaction_type IS NULL )
766: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
767: AND process_status = G_PROCESS_RECORD;
765: OR transaction_type IS NULL )
766: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
767: AND process_status = G_PROCESS_RECORD;
768:
769: UPDATE ego_functions_interface
770: SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
771: WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
772: OR transaction_type IS NULL )
773: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
812: G_REQUEST_ID,
813: G_PROGRAM_APPLICATION_ID,
814: G_PROGRAM_ID,
815: SYSDATE
816: FROM ego_functions_interface i
817: WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
818: AND NOT EXISTS (SELECT function_id
819: FROM ego_functions_b b
820: WHERE ( b.function_id = i.function_id ))
821: AND function_id IS NOT NULL
822: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
823: AND process_status = G_PROCESS_RECORD;
824:
825: UPDATE ego_functions_interface i
826: SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
827: WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
828: AND NOT EXISTS (SELECT *
829: FROM ego_functions_b b
872: G_REQUEST_ID,
873: G_PROGRAM_APPLICATION_ID,
874: G_PROGRAM_ID,
875: SYSDATE
876: FROM ego_functions_interface i
877: WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
878: AND NOT EXISTS (SELECT internal_name
879: FROM ego_functions_b b
880: WHERE ( b.internal_name = i.internal_name )
879: FROM ego_functions_b b
880: WHERE ( b.internal_name = i.internal_name )
881: UNION
882: SELECT internal_name
883: FROM ego_functions_interface ii
884: WHERE ( ii.internal_name = i.internal_name )
885: AND transaction_type = G_CREATE_TRANSACTION
886: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
887: AND process_status = G_PROCESS_RECORD)
889: AND internal_name IS NOT NULL
890: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
891: AND process_status = G_PROCESS_RECORD;
892:
893: UPDATE ego_functions_interface i
894: SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
895: WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
896: AND NOT EXISTS (SELECT internal_name
897: FROM ego_functions_b b
897: FROM ego_functions_b b
898: WHERE ( b.internal_name = i.internal_name )
899: UNION
900: SELECT internal_name
901: FROM ego_functions_interface ii
902: WHERE ( ii.internal_name = i.internal_name )
903: AND transaction_type = G_CREATE_TRANSACTION
904: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
905: AND process_status = G_PROCESS_RECORD)
947: G_REQUEST_ID,
948: G_PROGRAM_APPLICATION_ID,
949: G_PROGRAM_ID,
950: SYSDATE
951: FROM ego_functions_interface
952: WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
953: AND function_id IS NULL
954: AND internal_name IS NULL
955: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
954: AND internal_name IS NULL
955: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
956: AND process_status = G_PROCESS_RECORD;
957:
958: UPDATE ego_functions_interface i
959: SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
960: WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
961: AND function_id IS NULL
962: AND internal_name IS NULL
1667: -- This procedure will intialize function parameters interface table with by updating the --
1668: -- "WHO" columns, transaction_id and convering the transction_type to upper case. --
1669: -- Parameters: --
1670: -- IN --
1671: -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
1672: -- belongs to a particular batch. --
1673: ---------------------------------------------------------------------------------------------
1674: PROCEDURE initialize_func_params(p_set_process_id IN NUMBER)
1675: IS
1865: AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1866: AND process_status = G_PROCESS_RECORD;
1867:
1868: /* Validate validate internal_name if it is not null and function_id is not provided.
1869: We do not need to look into ego_functions_interface table for Internal Name, as we call
1870: this function only after processing ego_functions_interface table */
1871: message_name := 'EGO_INVALID_FUNC_INT_NAME';
1872: FND_MESSAGE.SET_NAME('EGO',message_name );
1873: message_text := FND_MESSAGE.GET;
1866: AND process_status = G_PROCESS_RECORD;
1867:
1868: /* Validate validate internal_name if it is not null and function_id is not provided.
1869: We do not need to look into ego_functions_interface table for Internal Name, as we call
1870: this function only after processing ego_functions_interface table */
1871: message_name := 'EGO_INVALID_FUNC_INT_NAME';
1872: FND_MESSAGE.SET_NAME('EGO',message_name );
1873: message_text := FND_MESSAGE.GET;
1874: