[Home] [Help]
1: package body FND_CONCURRENT as
2: /* $Header: AFCPUTLB.pls 120.12 2006/12/12 22:19:49 ckclark ship $ */
3: --
4: -- Package
5: -- FND_CONCURRENT
1: package body FND_CONCURRENT as
2: /* $Header: AFCPUTLB.pls 120.12 2006/12/12 22:19:49 ckclark ship $ */
3: --
4: -- Package
5: -- FND_CONCURRENT
6: -- Purpose
7: -- Concurrent processing related utilities
8: -- History
9: -- XX/XX/93 Ram Bhoopalam Created
18: PHASE_LOOKUP_TYPE constant varchar2(16) := 'CP_PHASE_CODE';
19: STATUS_LOOKUP_TYPE constant varchar2(16) := 'CP_STATUS_CODE';
20:
21: TYpe ConcProcessLocks Is Table of
22: Fnd_Concurrent_Processes.Lk_Handle%TYPE
23: Index By Binary_Integer;
24:
25: CmLkHandles ConcProcessLocks;
26:
41:
42: if ( apid = 0 AND cqid = 1 ) then
43: Select Max(Concurrent_Process_ID)
44: Into icm_cid
45: From Fnd_Concurrent_Processes
46: Where Process_Status_Code = 'A'
47: And (Queue_Application_ID = 0 And
48: Concurrent_Queue_ID = 1);
49:
74: when others then
75: oraerrmesg := substr(SQLERRM, 1, 80);
76: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
77: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
78: Fnd_Message.Set_Token('ROUTINE', 'FND_CONCURRENT.Get_Handle', FALSE);
79: return FALSE;
80: end get_handle;
81:
82: -- procedure is internal to this package.
230: end if;
231: begin
232: Select Concurrent_Program_ID, P.Application_ID
233: Into Program_ID, Prog_Appl_ID
234: From Fnd_Concurrent_Programs P,
235: Fnd_Application A
236: Where Concurrent_Program_Name = Program
237: And P.Application_ID = A.Application_ID
238: And A.Application_Short_Name = Appl_ShortName;
251: --
252: begin
253: Select Max(Request_ID)
254: Into Req_ID
255: From Fnd_Concurrent_Requests
256: Where Program_Application_ID = Prog_Appl_ID
257: And Concurrent_Program_ID = Program_ID;
258: --
259: -- If No rows returned, then return message saying there are no
285: Phase.Meaning, Status.Meaning
286: Into req_phase, req_status, comptext,
287: phase_code, status_code,
288: phasem, statusm
289: From Fnd_Concurrent_Requests R,
290: Fnd_Concurrent_programs P,
291: Fnd_Lookups Phase,
292: Fnd_Lookups Status
293: Where
286: Into req_phase, req_status, comptext,
287: phase_code, status_code,
288: phasem, statusm
289: From Fnd_Concurrent_Requests R,
290: Fnd_Concurrent_programs P,
291: Fnd_Lookups Phase,
292: Fnd_Lookups Status
293: Where
294: Phase.Lookup_Type = PHASE_LOOKUP_TYPE
320: when no_data_found then
321: Reqid_for_message := Req_ID;
322: Fnd_Message.Set_Name('FND', 'CONC-Request missing');
323: Fnd_Message.Set_Token('ROUTINE',
324: 'FND_CONCURRENT.GET_REQUEST_STATUS', FALSE);
325: Fnd_Message.Set_Token('REQUEST', Reqid_for_message, FALSE);
326: return FALSE;
327: when others then
328: raise; -- status_fetch_error
349: oraerrmesg := substr(SQLERRM, 1, 80);
350: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
351: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
352: Fnd_Message.Set_Token('ROUTINE',
353: 'FND_CONCURRENT.GET_REQUEST_STATUS', FALSE);
354: return FALSE;
355: end get_request_status;
356:
357:
407: Into STime From Sys.Dual;
408: end if;
409:
410: LOOP
411: call_status := FND_CONCURRENT.get_request_status(Rid, '', '',
412: phase, status, req_phase, dev_status, message);
413: if ( call_status = FALSE OR req_phase = 'COMPLETE' ) then
414: dev_phase := req_phase;
415: return (call_status);
433: oraerrmesg := substr(SQLERRM, 1, 80);
434: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
435: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
436: Fnd_Message.Set_Token('ROUTINE',
437: 'FND_CONCURRENT.WAIT_FOR_REQUEST', FALSE);
438: return FALSE;
439: end wait_for_request;
440:
441: --
439: end wait_for_request;
440:
441: --
442: -- Name
443: -- FND_CONCURRENT_GET_MANAGER_STATUS
444: -- Purpose
445: -- Returns the target ( number that should be active at this instant )
446: -- and active number of processes for a given manager.
447: -- along with the current PMON method currently in use
462: targetp OUT NOCOPY number,
463: activep OUT NOCOPY number,
464: pmon_method OUT NOCOPY varchar2,
465: callstat OUT NOCOPY number) is
466: lkh FND_CONCURRENT_PROCESSES.Lk_Handle%TYPE;
467: result number; -- result code from DBMS_LOCK.Request_Lock
468: alive number; -- is process alive? 1=TRUE 0=FALSE
469: i number := 0;
470: errflag boolean := FALSE;
474: cur_session_id number;
475:
476: Cursor C1 IS
477: Select Concurrent_Process_Id, Session_Id
478: From Fnd_Concurrent_Processes
479: Where Process_Status_Code in ( 'A', 'C', 'T' )
480: And (Queue_Application_ID = applid and
481: Concurrent_Queue_ID = managerid );
482: begin
489: /* from FCQ */
490: select manager_type,
491: Running_processes, MAX_PROCESSES, Cartridge_Handle
492: into mtype, ActiveP, TargetP, CartType
493: from Fnd_Concurrent_Queues Q, Fnd_Cp_Services S
494: Where S.Service_ID = Q.Manager_Type
495: And (Q.Application_ID = applid
496: And Q.Concurrent_Queue_ID = managerid);
497:
498: if (mtype>999) then
499: if (CartType = 'AQCART') then
500: select count(*)
501: into ActiveP
502: from gv$session GV, fnd_concurrent_processes P
503: where
504: GV.Inst_id = P.Instance_number
505: And GV.audsid = p.session_id
506: And (Process_Status_Code not in ('S','K','U'))
546: return;
547: end if;
548: end if;
549:
550: Select Max_Processes Into TargetP From Fnd_Concurrent_Queues
551: Where Concurrent_Queue_ID = ManagerID
552: And Application_ID = ApplID;
553:
554:
605: oraerrmesg := substr(SQLERRM, 1, 80);
606: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
607: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
608: Fnd_Message.Set_Token('ROUTINE',
609: 'FND_CONCURRENT.GET_MANAGER_STATUS', FALSE);
610: return;
611: end get_manager_status;
612:
613: -- Name
610: return;
611: end get_manager_status;
612:
613: -- Name
614: -- FND_CONCURRENT.SET_STATUS_AUTONUMOUS
615: -- Purpose
616: -- Updates given request status and completion text in an autonomous
617: -- transaction. This is function is called in set_completion_status
618: -- function. (Internal use only).
634: PRAGMA AUTONOMOUS_TRANSACTION;
635: begin
636: if ( interim ) then
637: if(upper(status) = 'W') then
638: update fnd_concurrent_requests
639: set interim_status_code = 'W',
640: req_information = substrb(message,1,240)
641: where request_id = set_status_autonomous.request_id;
642: --debug('updated req_information for request_id '|| set_status_autonomous.request_id);
640: req_information = substrb(message,1,240)
641: where request_id = set_status_autonomous.request_id;
642: --debug('updated req_information for request_id '|| set_status_autonomous.request_id);
643: else
644: update fnd_concurrent_requests
645: set interim_status_code = set_status_autonomous.status,
646: completion_text = substrb(message, 1, 240)
647: where request_id = set_status_autonomous.request_id;
648: --debug('updated completion_text for request_id '|| set_status_autonomous.request_id);
648: --debug('updated completion_text for request_id '|| set_status_autonomous.request_id);
649: end if;
650:
651: else
652: update fnd_concurrent_requests
653: set phase_code = 'C',
654: status_code = set_status_autonomous.status,
655: completion_text = substrb(message, 1, 240)
656: where request_id = set_status_autonomous.request_id;
669: end;
670:
671: --
672: -- Name
673: -- FND_CONCURRENT.SET_COMPLETION_STATUS
674: -- Purpose
675: -- Called from a concurrent request to set its completion
676: -- status and message.
677: --
714: if ( nvl(lengthb(ret_str), 0 ) > 0 ) then
715: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
716: Fnd_Message.Set_Token('ERROR', ret_str, FALSE);
717: Fnd_Message.Set_Token('ROUTINE',
718: 'FND_CONCURRENT.SET_COMPLETION_STATUS', FALSE);
719: return FALSE;
720: else
721: return TRUE;
722: end if;
740: save_output_flag OUT NOCOPY varchar2) return boolean is
741: begin
742: select number_of_copies, print_style, printer, save_output_flag
743: into number_of_copies, print_style, printer, save_output_flag
744: from fnd_concurrent_requests r
745: where r.request_id = get_request_print_options.request_id;
746:
747: return TRUE;
748:
751: oraerrmesg := substr(SQLERRM, 1, 80);
752: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
753: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
754: Fnd_Message.Set_Token('ROUTINE',
755: 'FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS', FALSE);
756: return FALSE;
757: end get_request_print_options;
758:
759: --
775: counter number := 0;
776: cursor c1 is
777: select p.number_of_copies, r.print_style,
778: p.arguments, r.save_output_flag
779: from fnd_concurrent_requests r,
780: fnd_conc_pp_actions p
781: where r.request_id = p.concurrent_request_id
782: and p.action_type = 1
783: and p.concurrent_request_id = get_request_print_options.request_id
797: oraerrmesg := substr(SQLERRM, 1, 80);
798: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
799: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
800: Fnd_Message.Set_Token('ROUTINE',
801: 'FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS', FALSE);
802: return counter;
803: end get_request_print_options;
804:
805: --
833: --
834: if (C_SessionID is NULL ) then
835: Select Session_ID
836: into C_SessionID
837: from fnd_concurrent_processes cp,
838: fnd_concurrent_queues cq
839: where process_status_code = 'A'
840: and cp.Queue_Application_ID = cq.application_ID
841: and cp.concurrent_queue_id = cq.concurrent_queue_id
834: if (C_SessionID is NULL ) then
835: Select Session_ID
836: into C_SessionID
837: from fnd_concurrent_processes cp,
838: fnd_concurrent_queues cq
839: where process_status_code = 'A'
840: and cp.Queue_Application_ID = cq.application_ID
841: and cp.concurrent_queue_id = cq.concurrent_queue_id
842: and cq.concurrent_queue_name = Queue_Name;
884: oraerrmesg := substr(SQLERRM, 1, 80);
885: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
886: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
887: Fnd_Message.Set_Token('ROUTINE',
888: 'FND_CONCURRENT.Check_Lock_Contention', FALSE);
889: return FALSE;
890:
891: end Check_Lock_Contention;
892:
907: ltype varchar2(8) := 'YES_NO';
908: begin
909: select PRINTER_NAME, user_printer_style_name, l.meaning
910: into printer, style, save_output
911: from fnd_concurrent_programs p, fnd_printer_styles_VL ps,
912: fnd_lookups L, fnd_application_vl A
913: where
914: l.lookup_code = p.SAVE_OUTPUT_FLAG
915: and l.lookup_type = ltype
930: oraerrmesg := substr(SQLERRM, 1, 80);
931: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
932: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
933: Fnd_Message.Set_Token('ROUTINE',
934: 'FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES', FALSE);
935: return FALSE;
936: end get_program_attributes;
937:
938: --
936: end get_program_attributes;
937:
938: --
939: -- Name
940: -- FND_CONCURRENT.SET_COMPLETION_STATUS
941: -- Purpose
942: -- Called from a concurrent request to set its completion
943: -- status and message.
944: --
985: From V$Session S, V$Process P
986: Where P.Addr = S.Paddr
987: and S.AUDSID = userenv('SESSIONID');
988:
989: update fnd_concurrent_requests
990: set ORACLE_SESSION_ID = csid,
991: ORACLE_PROCESS_ID = csspid,
992: OS_PROCESS_ID = cspid,
993: NLS_CodeSet = codeset
1005: execution_method_code,
1006: multi_org_category, org_id
1007: into optmode, program_name, ptrace, rtrace, etstat, emethod,
1008: morg_cat, orgid
1009: from FND_CONCURRENT_PROGRAMS P,
1010: FND_CONCURRENT_REQUESTS R
1011: WHERE P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
1012: And P.APPLICATION_ID = R.Program_APPLICATION_ID
1013: And R.request_id = fnd_global.conc_request_id;
1006: multi_org_category, org_id
1007: into optmode, program_name, ptrace, rtrace, etstat, emethod,
1008: morg_cat, orgid
1009: from FND_CONCURRENT_PROGRAMS P,
1010: FND_CONCURRENT_REQUESTS R
1011: WHERE P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
1012: And P.APPLICATION_ID = R.Program_APPLICATION_ID
1013: And R.request_id = fnd_global.conc_request_id;
1014: exception
1026: end if;
1027:
1028: Select plsql_log, plsql_out, plsql_dir
1029: Into plog, pout, pdir
1030: From Fnd_Concurrent_Processes P, Fnd_Concurrent_Requests R
1031: Where P.Concurrent_Process_ID = R.Controlling_Manager
1032: And R.Request_ID = fnd_global.conc_request_id;
1033:
1034: fnd_file.put_names(plog, pout, pdir);
1049: 'Concurrent Request');
1050: begin
1051: select Q.RESOURCE_CONSUMER_GROUP
1052: into que_rcg
1053: from fnd_concurrent_requests r,
1054: fnd_concurrent_processes p,
1055: fnd_concurrent_queues q
1056: where R.request_id = fnd_global.conc_request_id
1057: and R.controlling_manager = P.concurrent_process_id
1050: begin
1051: select Q.RESOURCE_CONSUMER_GROUP
1052: into que_rcg
1053: from fnd_concurrent_requests r,
1054: fnd_concurrent_processes p,
1055: fnd_concurrent_queues q
1056: where R.request_id = fnd_global.conc_request_id
1057: and R.controlling_manager = P.concurrent_process_id
1058: and Q.CONCURRENT_QUEUE_ID= P.CONCURRENT_QUEUE_ID
1051: select Q.RESOURCE_CONSUMER_GROUP
1052: into que_rcg
1053: from fnd_concurrent_requests r,
1054: fnd_concurrent_processes p,
1055: fnd_concurrent_queues q
1056: where R.request_id = fnd_global.conc_request_id
1057: and R.controlling_manager = P.concurrent_process_id
1058: and Q.CONCURRENT_QUEUE_ID= P.CONCURRENT_QUEUE_ID
1059: and Q.APPLICATION_ID = P.QUEUE_APPLICATION_ID;
1064:
1065: begin
1066: select p.RESOURCE_CONSUMER_GROUP
1067: into prg_rcg
1068: from fnd_concurrent_programs P,
1069: fnd_concurrent_requests R
1070: where R.request_id = fnd_global.conc_request_id
1071: and r.PROGRAM_APPLICATION_ID = P.APPLICATION_ID
1072: and R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID;
1065: begin
1066: select p.RESOURCE_CONSUMER_GROUP
1067: into prg_rcg
1068: from fnd_concurrent_programs P,
1069: fnd_concurrent_requests R
1070: where R.request_id = fnd_global.conc_request_id
1071: and r.PROGRAM_APPLICATION_ID = P.APPLICATION_ID
1072: and R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID;
1073: exception
1108: oraerrmesg := substr(SQLERRM, 1, 80);
1109: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
1110: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
1111: Fnd_Message.Set_Token('ROUTINE',
1112: 'FND_CONCURRENT.INIT_REQUEST', FALSE);
1113: return;
1114:
1115: end init_request;
1116:
1116:
1117:
1118: --
1119: -- Name
1120: -- FND_CONCURRENT.SET_PREFERRED_RBS
1121: -- Purpose
1122: -- Called from afpirq, etc to set the Rollback Segment associated with req.
1123: --
1124: -- Arguments (input)
1132:
1133: begin
1134: select P.Rollback_Segment
1135: into RBS
1136: from FND_CONCURRENT_PROGRAMS P,
1137: FND_CONCURRENT_REQUESTS R
1138: WHERE R.request_id = fnd_global.conc_request_id
1139: AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
1140: And R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID;
1133: begin
1134: select P.Rollback_Segment
1135: into RBS
1136: from FND_CONCURRENT_PROGRAMS P,
1137: FND_CONCURRENT_REQUESTS R
1138: WHERE R.request_id = fnd_global.conc_request_id
1139: AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
1140: And R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID;
1141:
1154: end SET_PREFERRED_RBS;
1155:
1156: --
1157: -- Name
1158: -- Fnd_Concurrent.Reset_Context
1159: -- Purpose
1160: -- To reset/re-establish context that may have been lost due to commits
1161: --
1162: -- Arguments (input)
1177: if (g_request_id is null) then
1178: g_request_id := Request_Id;
1179: end if;
1180:
1181: Fnd_Concurrent.Set_Preferred_RBS;
1182:
1183: return TRUE;
1184:
1185: exception
1188:
1189: end Reset_Context;
1190:
1191: -- Name
1192: -- FND_CONCURRENT.AF_COMMIT
1193: -- Purpose
1194: -- It does the commit and set the preferred rollback segment for the
1195: -- program. Call this routine only in the concurrent program context.
1196: --
1205:
1206: -- if the context is concurrent program then set the rollback segment to
1207: -- program preferred
1208: if (fnd_global.conc_request_id > 0) then
1209: fnd_concurrent.set_preferred_rbs;
1210: end if;
1211:
1212: End AF_COMMIT;
1213:
1211:
1212: End AF_COMMIT;
1213:
1214: -- Name
1215: -- FND_CONCURRENT.AF_ROLLBACK
1216: -- Purpose
1217: -- It does the rollback and set the preferred rollback segment for the
1218: -- program. Call this routine only in the concurrent program context.
1219: --
1228:
1229: -- if the context is concurrent program then set the rollback segment to
1230: -- program preferred
1231: if (fnd_global.conc_request_id > 0) then
1232: fnd_concurrent.set_preferred_rbs;
1233: end if;
1234:
1235: End AF_ROLLBACK;
1236:
1234:
1235: End AF_ROLLBACK;
1236:
1237: -- Name
1238: -- FND_CONCURRENT.SHUT_DOWN_PROCS
1239: -- Purpose
1240: -- Runs the pl/sql shutdown procedures stored in FND_EXECUTABLES
1241: -- with EXECUTION_METHOD_CODE = 'Z'.
1242: --
1264: if ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1265: fnd_message.set_name('FND', 'CONC-SHUTDOWN_ACTION_FAILURE');
1266: fnd_message.set_token('PROCEDURE', c_rec.execution_file_name);
1267: fnd_message.set_token('REASON', errbuf);
1268: fnd_log.message(FND_LOG.LEVEL_ERROR, 'fnd.plsql.fnd_concurrent.shut_down_procs', FALSE);
1269: end if;
1270: end;
1271: END LOOP;
1272:
1272:
1273: end shut_down_procs;
1274:
1275: -- Name
1276: -- FND_CONCURRENT.SET_INTERIM_STATUS
1277: -- Purpose
1278: -- sets the requests phase_code, interim_status_code and completion_text
1279: -- this is used in Java Concurrent Programs.
1280: --
1334: if ( nvl(lengthb(ret_str), 0 ) > 0 ) then
1335: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
1336: Fnd_Message.Set_Token('ERROR', ret_str, FALSE);
1337: Fnd_Message.Set_Token('ROUTINE',
1338: 'FND_CONCURRENT.SET_INTERIM_STATUS', FALSE);
1339: return FALSE;
1340: else
1341: return TRUE;
1342: end if;
1343:
1344: end set_interim_status;
1345:
1346: -- Name
1347: -- FND_CONCURRENT.GET_SUB_REQUESTS
1348: -- Purpose
1349: -- gets all sub-requests for a given request id. For each sub-request it
1350: -- provides request_id, phase,status, developer phase , developer status
1351: -- completion text.
1353: -- Arguments (input)
1354: -- request_id - Request Id for which sub-requests are required.
1355: --
1356: -- Returns:
1357: -- Table FND_CONCURRENT.REQUESTS_TAB_TYPE.
1358: --
1359:
1360: function get_sub_requests( p_request_id IN number)
1361: return requests_tab_type is
1363: Select Request_Id, Completion_Text,
1364: Phase.Lookup_Code p_lookup_code,
1365: Status.Lookup_Code s_lookup_code,
1366: Phase.Meaning p_meaning, Status.Meaning s_meaning
1367: From Fnd_Concurrent_Requests R,
1368: Fnd_Concurrent_programs P,
1369: Fnd_Lookups Phase,
1370: Fnd_Lookups Status
1371: Where
1364: Phase.Lookup_Code p_lookup_code,
1365: Status.Lookup_Code s_lookup_code,
1366: Phase.Meaning p_meaning, Status.Meaning s_meaning
1367: From Fnd_Concurrent_Requests R,
1368: Fnd_Concurrent_programs P,
1369: Fnd_Lookups Phase,
1370: Fnd_Lookups Status
1371: Where
1372: Phase.Lookup_Type = PHASE_LOOKUP_TYPE
1416: return sub_reqs;
1417: end get_sub_requests;
1418:
1419: -- Name
1420: -- FND_CONCURRENT.CHILDREN_DONE
1421: -- Purpose
1422: -- Examines all child requests of a given request id. Returns TRUE if
1423: -- all have completed. Does not consider grandchildren or parent
1424: --
1442: time_left number;
1443: parent_req_id number;
1444: cursor kidslist(parent_id number) is
1445: Select request_id
1446: from fnd_concurrent_requests
1447: where parent_request_id = parent_id;
1448:
1449: begin
1450: Select sysdate + (greatest(Max_Wait, 0)/86400)
1458: if (parent_req_id = -1) then return TRUE; end if;
1459:
1460: LOOP
1461: Select count(*) into kount
1462: from fnd_concurrent_requests
1463: where parent_request_id = parent_req_id
1464: and phase_code <> 'C';
1465:
1466: if (kount = 0) AND (Recursive_Flag = 'Y') then -- check for kids
1578: end if;
1579: end register_node;
1580:
1581: -- Name
1582: -- Fnd_Concurrent.Get_Service_Instances
1583: -- Purpose
1584: -- Fetch all service instances defined for a Service type
1585: -- Returns the service instance identity along with it's current
1586: -- state (Active/Disabled/Inactive/Suspended/Transit )
1588: -- Arguments (input)
1589: -- svc_handle - Developer name for the Service type
1590: --
1591: -- Returns:
1592: -- Table Fnd_Concurrent.Service_Instance_Tab_Type. A table size of 0
1593: -- indicates absence of any service instances for the specified service
1594: -- type
1595: --
1596:
1604: RUNNING_PROCESSES c_run_procs,
1605: MAX_PROCESSES c_max_procs,
1606: CONTROL_CODE c_ctrl_code,
1607: ENABLED_FLAG c_enabled
1608: from FND_CONCURRENT_QUEUES fcq,
1609: FND_CP_SERVICES fcs,
1610: FND_APPLICATION fa
1611: where
1612: fcq.MANAGER_TYPE = to_char(fcs.SERVICE_id)
1649: return svc_inst_inf;
1650: end Get_Service_Instances;
1651:
1652: -- Name
1653: -- Fnd_Concurrent.Get_Service_Processes
1654: -- Purpose
1655: -- Fetch all service instance processes for a service instance
1656: --
1657: -- Arguments (input)
1662: --
1663: -- Application and Service Instance Name together can be used to locate
1664: -- all processes
1665: --
1666: -- Returns (Fnd_Concurrent.Service_Process_Tab_Type)
1667: -- Fnd_Concurrent.Service_Process_Tab_Type.
1668: -- CPID (Concurrent_Process_ID) - Can be used to address/act on the
1669: -- process
1670: -- Service_Parameters - To be used to target particular
1663: -- Application and Service Instance Name together can be used to locate
1664: -- all processes
1665: --
1666: -- Returns (Fnd_Concurrent.Service_Process_Tab_Type)
1667: -- Fnd_Concurrent.Service_Process_Tab_Type.
1668: -- CPID (Concurrent_Process_ID) - Can be used to address/act on the
1669: -- process
1670: -- Service_Parameters - To be used to target particular
1671: -- - service instances
1684: select CONCURRENT_PROCESS_ID c_cpid,
1685: MEANING c_state,
1686: fcp.NODE_NAME c_node,
1687: fcp.SERVICE_PARAMETERS c_parameters
1688: from FND_CONCURRENT_QUEUES fcq,
1689: FND_CONCURRENT_PROCESSES fcp,
1690: FND_APPLICATION fa,
1691: FND_LOOKUP_VALUES_VL flv
1692: where
1685: MEANING c_state,
1686: fcp.NODE_NAME c_node,
1687: fcp.SERVICE_PARAMETERS c_parameters
1688: from FND_CONCURRENT_QUEUES fcq,
1689: FND_CONCURRENT_PROCESSES fcp,
1690: FND_APPLICATION fa,
1691: FND_LOOKUP_VALUES_VL flv
1692: where
1693: fcp.QUEUE_APPLICATION_ID = fcq.APPLICATION_ID
1716: -- Validate service application and name
1717: begin
1718: select 0
1719: into i
1720: from FND_CONCURRENT_QUEUES fcq,
1721: FND_APPLICATION fa
1722: where fcq.APPLICATION_ID = fa.APPLICATION_ID
1723: and APPLICATION_SHORT_NAME = upper(appl_short_name)
1724: and upper(CONCURRENT_QUEUE_NAME) = upper(svc_instance_name);
1744: return svc_proc_inf;
1745: end Get_Service_Processes;
1746:
1747: -- Name
1748: -- FND_CONCURRENT.MSC_MATCH_BY_SERVICE_TYPE
1749: -- Purpose
1750: -- internal function used to find matches of svc cntl requests and
1751: -- Service types. returns 1 for a match, 0 for no.
1752:
1763: ugly piece of coding where we decipher if we have a hit */
1764:
1765: select argument1, argument2, argument3
1766: into rarg1, rarg2, rarg3
1767: from fnd_concurrent_requests R
1768: where requestid = R.request_id;
1769:
1770: /* CASE positive : old style requests */
1771: if (rarg1 >= 0) then
1771: if (rarg1 >= 0) then
1772:
1773: select count(concurrent_queue_id)
1774: into kount
1775: from fnd_concurrent_queues
1776: where concurrent_queue_id = rarg1
1777: and application_id = rarg2
1778: and manager_type = mtype;
1779:
1792:
1793:
1794: select count(concurrent_queue_id)
1795: into kount
1796: from fnd_concurrent_queues
1797: where application_id = rarg2
1798: and manager_type = mtype;
1799:
1800: if (kount > 0) then return 1;
1830:
1831: end MSC_MATCH_BY_SERVICE_TYPE;
1832:
1833: -- Name
1834: -- FND_CONCURRENT.MSC_MATCH
1835: -- Purpose
1836: -- internal function used to find matches of svc cntl requests and services
1837: -- or managers. returns 1 for a match, 0 for no.
1838:
1853: ugly piece of coding where we decipher if we have a hit */
1854:
1855: select argument1, argument2, argument3
1856: into rarg1, rarg2, rarg3
1857: from fnd_concurrent_requests R
1858: where requestid = R.request_id;
1859:
1860: /* CASE positive : old style requests */
1861: if ((rarg1 >= 0) and (rarg1 = que_id) and (rarg2 = app_id)) then
1898: end if;
1899:
1900: end MSC_MATCH;
1901: -- Name
1902: -- FND_CONCURRENT.find_pending_svc_ctrl_reqs
1903: -- Purpose
1904: -- gets all pending service control requests for a given service or service
1905: -- instance. Returns number of requests found and has an out parameter
1906: -- containing a comma delimited list of matching requests.
1926: my_service_id number := service_id;
1927:
1928: Cursor C1 IS
1929: Select request_id
1930: from fnd_concurrent_requests R, fnd_concurrent_programs P
1931: where r.phase_code = 'P'
1932: and p.application_id = r.PROGRAM_APPLICATION_ID
1933: and p.concurrent_program_id = r.concurrent_program_id
1934: and p.queue_control_flag = 'Y'
1944: elsif (service_id is null) then
1945: /* populate app and service id */
1946: select manager_type, application_id
1947: into my_service_id, app_id
1948: from fnd_concurrent_queues
1949: where concurrent_queue_id = service_inst_id;
1950: end if;
1951:
1952: /* find results */
1964: end find_pending_svc_ctrl_reqs;
1965:
1966:
1967: -- Name
1968: -- FND_CONCURRENT.Find_SC_Conflict
1969: -- Purpose
1970: -- Finds later conflicting service control request (if any) for another
1971: -- service control request.
1972: --
1981: kount number := 0;
1982:
1983: Cursor C1 IS
1984: Select R2.request_id
1985: from fnd_concurrent_requests R1,
1986: fnd_concurrent_requests R2,
1987: fnd_concurrent_programs P1,
1988: fnd_concurrent_programs P2,
1989: fnd_concurrent_queues Q,
1982:
1983: Cursor C1 IS
1984: Select R2.request_id
1985: from fnd_concurrent_requests R1,
1986: fnd_concurrent_requests R2,
1987: fnd_concurrent_programs P1,
1988: fnd_concurrent_programs P2,
1989: fnd_concurrent_queues Q,
1990: fnd_application A
1983: Cursor C1 IS
1984: Select R2.request_id
1985: from fnd_concurrent_requests R1,
1986: fnd_concurrent_requests R2,
1987: fnd_concurrent_programs P1,
1988: fnd_concurrent_programs P2,
1989: fnd_concurrent_queues Q,
1990: fnd_application A
1991: where r1.request_id = reqid
1984: Select R2.request_id
1985: from fnd_concurrent_requests R1,
1986: fnd_concurrent_requests R2,
1987: fnd_concurrent_programs P1,
1988: fnd_concurrent_programs P2,
1989: fnd_concurrent_queues Q,
1990: fnd_application A
1991: where r1.request_id = reqid
1992: and P1.APPLICATION_ID = R1.PROGRAM_APPLICATION_ID
1985: from fnd_concurrent_requests R1,
1986: fnd_concurrent_requests R2,
1987: fnd_concurrent_programs P1,
1988: fnd_concurrent_programs P2,
1989: fnd_concurrent_queues Q,
1990: fnd_application A
1991: where r1.request_id = reqid
1992: and P1.APPLICATION_ID = R1.PROGRAM_APPLICATION_ID
1993: and P1.concurrent_program_id = R1.concurrent_program_id
2024: /* CASE positive : old style requests */
2025: if (rarg1 >= 0) then
2026: select count(concurrent_queue_id)
2027: into kount
2028: from fnd_concurrent_queues
2029: where concurrent_queue_id = rarg1
2030: and application_id = rarg2
2031: and ((Running_processes <> MAX_PROCESSES)
2032: or ((goal_state is not null) and
2037: /* CASE -1 : By app id */
2038: elsif (rarg1 = -1) then
2039: select count(concurrent_queue_id)
2040: into kount
2041: from fnd_concurrent_queues
2042: where application_id = rarg2
2043: and (
2044: /* either CM or TM and request is for mgrs (or both) */
2045: (((manager_type = 1) or (manager_type = 3))
2054: /* CASE -2 : cp fun pak */
2055: elsif (rarg1 = -2) then
2056: select count(concurrent_queue_id)
2057: into kount
2058: from fnd_concurrent_queues
2059: where manager_type IN ('1', '3', '4', '5')
2060: and ((Running_processes <> MAX_PROCESSES)
2061: or ((goal_state is not null) and
2062: ((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
2066: /* CASE -3 : By service type */
2067: elsif (rarg1 = -3) then
2068: select count(concurrent_queue_id)
2069: into kount
2070: from fnd_concurrent_queues
2071: where manager_type = to_char(rarg2)
2072: and ((Running_processes <> MAX_PROCESSES)
2073: or ((goal_state is not null) and
2074: ((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
2077: /* CASE -4 : cp all */
2078: elsif (rarg1 = -4) then
2079: select count(concurrent_queue_id)
2080: into kount
2081: from fnd_concurrent_queues
2082: where manager_type < 1000
2083: and ((Running_processes <> MAX_PROCESSES)
2084: or ((goal_state is not null) and
2085: ((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
2096:
2097:
2098:
2099: -- Name
2100: -- FND_CONCURRENT.Function Wait_for_SCTL_Done
2101: -- Purpose
2102: -- Waits for Svc Ctrl request to finish, or another conflicting request,
2103: -- or timeout.
2104: --
2143: select concurrent_program_id, program_application_id, phase_code,
2144: argument1, argument2, argument3, Decode(concurrent_program_id,
2145: 0,null, 1,'E', 3, null, 4, 'X', 5, 'E', 7, 'P', 8, null, null)
2146: into prog_id, r_app_id, r_phase, rarg1, rarg2, rarg3, goal_state
2147: from fnd_concurrent_requests
2148: where request_id = reqid;
2149: exception
2150: when others then
2151: return SCTL_REQ_NOT_FOUND;
2182:
2183: /* have we run yet? */
2184: select decode(phase_code, 'C', 0, -1)
2185: into Done
2186: from fnd_concurrent_requests
2187: where request_id = reqid;
2188:
2189: if ((timesup > 0) and (Done = 0)) then
2190: return SCTL_TIMEOUT_NOT_C;
2228: end SCTL_REQ_COMPLETED;
2229:
2230:
2231: -- Name
2232: -- FND_CONCURRENT.Wait_For_All_Down
2233: -- Purpose
2234: -- Waits for all services, managers, and icm to go down, or timesout.
2235: --
2236: -- Arguments (input)
2279: oraerrmesg := substr(SQLERRM, 1, 80);
2280: Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
2281: Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
2282: Fnd_Message.Set_Token('ROUTINE',
2283: 'FND_CONCURRENT.WAIT_FOR_ALL_DOWN', FALSE);
2284: return FALSE;
2285: end Wait_For_All_Down;
2286:
2287:
2285: end Wait_For_All_Down;
2286:
2287:
2288: -- Name
2289: -- FND_CONCURRENT.Build_Svc_Ctrl_Desc.
2290: -- Purpose
2291: -- Provides description text for svc ctrl request based on args.
2292: --
2293: -- Arguments (input)
2310: begin
2311: begin
2312: select cp.USER_CONCURRENT_PROGRAM_NAME
2313: into action
2314: from fnd_concurrent_programs_vl cp, fnd_application a
2315: where cp.concurrent_program_name = prog
2316: AND cp.application_id = a.application_id
2317: AND a.application_short_name = 'FND';
2318: exception when others then
2323:
2324: begin
2325: select USER_CONCURRENT_QUEUE_NAME
2326: into Detail
2327: from fnd_concurrent_queues_vl
2328: where APPLICATION_ID = Arg2
2329: and concurrent_queue_id = Arg1;
2330: exception
2331: when others then
2398:
2399: end Build_Svc_Ctrl_Desc;
2400:
2401: -- Name
2402: -- FND_CONCURRENT.Cancel_Request.
2403: -- Purpose
2404: -- It Cancels given Concurrent Request.
2405: --
2406: -- Arguments (input)
2423: -- check this user got privilege to cancel request
2424: begin
2425: Select Requested_By
2426: into submitter
2427: from fnd_concurrent_requests
2428: where request_id = Cancel_Request.request_id;
2429: exception
2430: when no_data_found then
2431: raise request_missing;
2457:
2458: exception
2459: when request_missing then
2460: fnd_message.set_name('FND', 'CONC-MISSING REQUEST');
2461: fnd_message.set_token('ROUTINE', 'FND_CONCURRENT.CANCEL_REQUEST');
2462: fnd_message.set_token('REQUEST', to_char(request_id));
2463: message := fnd_message.get;
2464: return FALSE;
2465: when no_privilege then
2468: message := fnd_message.get;
2469: return FALSE;
2470: when others then
2471: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2472: fnd_message.set_token('ROUTINE', 'FND_CONCURRENT.CANCEL_REQUEST');
2473: fnd_message.set_token('ERRNO', SQLCODE);
2474: fnd_message.set_token('REASON', SQLERRM);
2475: message := fnd_message.get;
2476: return FALSE;
2478: end Cancel_Request;
2479:
2480: --
2481: -- Name
2482: -- FND_CONCURRENT.get_resource_lock
2483: -- Purpose
2484: -- It gets an exclusive lock for a given resource or task name.
2485: --
2486: -- Arguments (input)
2520: exception
2521: when others then
2522: fnd_message.set_name ('FND', 'CP-Generic oracle error');
2523: fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
2524: fnd_message.set_token ('ROUTINE', 'fnd_concurrent.get_resource_lock',
2525: FALSE);
2526: return result;
2527:
2528: end;
2528: end;
2529:
2530: --
2531: -- Name
2532: -- FND_CONCURRENT.release_resource_lock
2533: -- Purpose
2534: -- It releases an exclusive lock for a given resource or task name.
2535: --
2536: -- Arguments (input)
2562: exception
2563: when others then
2564: fnd_message.set_name ('FND', 'CP-Generic oracle error');
2565: fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
2566: fnd_message.set_token ('ROUTINE', 'fnd_concurrent.release_resource_lock',
2567: FALSE);
2568: return result;
2569:
2570: end;
2576:
2577:
2578: --
2579: -- Name
2580: -- FND_CONCURRENT.INIT_SQL_REQUEST
2581: -- Purpose
2582: -- Called for all SQL*PLUS concurrent requests to perform request initialization.
2583: --
2584:
2602: lmachine v$session.machine%type;
2603: position number;
2604: begin
2605:
2606: -- Select all the information needed for this request from fnd_concurrent_requests,
2607: -- using the fnd_cp_sql_requests table.
2608: -- A row should have been inserted earlier in usdspid, containing the current request id,
2609: -- machine name, and process id.
2610: -- By joining these tables with v$session, we can pull out all the information we need,
2634: fcr.request_id, fcr.priority_request_id
2635: into session_id, userid, respid, respappid,
2636: secgrpid, siteid, loginid, cloginid,
2637: progappid, cprogid, creqid, cprireqid
2638: from fnd_concurrent_requests fcr,
2639: fnd_cp_sql_requests sr
2640: where fcr.phase_code = 'R'
2641: and fcr.status_code = 'R'
2642: and fcr.request_id = sr.request_id
2682: if ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2683: fnd_message.set_name ('FND', 'SQL-Generic error');
2684: fnd_message.set_token ('ERRNO', sqlcode, FALSE);
2685: fnd_message.set_token ('REASON', sqlerrm, FALSE);
2686: fnd_message.set_token ('ROUTINE', 'FND_CONCURRENT.INIT_SQL_REQUEST', FALSE);
2687: fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2688: 'fnd.plsql.FND_CONCURRENT.INIT_SQL_REQUEST.others', FALSE);
2689: end if;
2690: end;
2684: fnd_message.set_token ('ERRNO', sqlcode, FALSE);
2685: fnd_message.set_token ('REASON', sqlerrm, FALSE);
2686: fnd_message.set_token ('ROUTINE', 'FND_CONCURRENT.INIT_SQL_REQUEST', FALSE);
2687: fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2688: 'fnd.plsql.FND_CONCURRENT.INIT_SQL_REQUEST.others', FALSE);
2689: end if;
2690: end;
2691:
2692: end init_sql_request;
2696: -- msg varchar2(2000);
2697: -- l_count number;
2698: -- begin
2699:
2700: -- insert into FND_CONCURRENT_DEBUG_INFO(TIME, ACTION, message, TIME_IN_NUMBER)
2701: -- VALUES(sysdate,'FND_CONCURRENT.get_m_s',message,0);
2702: --commit;
2703: --end debug;
2704:
2697: -- l_count number;
2698: -- begin
2699:
2700: -- insert into FND_CONCURRENT_DEBUG_INFO(TIME, ACTION, message, TIME_IN_NUMBER)
2701: -- VALUES(sysdate,'FND_CONCURRENT.get_m_s',message,0);
2702: --commit;
2703: --end debug;
2704:
2705:
2782:
2783: CURSOR l_prog_id_csr(p_program_name varchar2,
2784: p_application_id number) IS
2785: SELECT concurrent_program_id, srs_flag
2786: FROM fnd_concurrent_programs
2787: WHERE concurrent_program_name = p_program_name
2788: AND application_id = p_application_id;
2789:
2790: BEGIN
2826:
2827: fnd_data_security.get_security_predicate
2828: (p_api_version => 1.0,
2829: p_function => 'FND_CP_REQ_SUBMIT',
2830: p_object_name => 'FND_CONCURRENT_PROGRAMS',
2831: x_predicate => l_predicate,
2832: x_return_status => l_return_status,
2833: p_table_alias => 'p');
2834:
2831: x_predicate => l_predicate,
2832: x_return_status => l_return_status,
2833: p_table_alias => 'p');
2834:
2835: l_sql_stmt := 'select count(p.concurrent_program_id) from fnd_concurrent_programs p where p.concurrent_program_id = :1 and p.application_id = :2 and ' || l_predicate || '';
2836:
2837: execute immediate l_sql_stmt INTO l_ret_value using l_conc_program_id, l_application_id;
2838:
2839: IF (l_ret_value > 0) THEN
2850: RETURN l_ret_value;
2851: END check_program_privileges;
2852:
2853:
2854: end FND_CONCURRENT;