222: /*
223: NAME
224: lock_pop_chunk - Lock population Chunk
225: DESCRIPTION
226: This locks the population Chunk using the PAY_CHUNK_STATUS table
227: NOTES
228:
229: */
230: procedure lock_pop_chunk
238: rand_chunk out nocopy number -- chunk_number.
239: ) is
240: l_lckhandle varchar2(128);
241: ret number;
242: chk_pop_status pay_chunk_status.population_status%type;
243: act_pop_status pay_payroll_actions.action_population_status%type;
244: begin
245: /* OK we have the next chunk lets lock it and confirm that its
246: unprocessed
246: unprocessed
247: */
248: --
249: dbms_lock.allocate_unique(
250: lockname => 'PAY_CHUNK_STATUS'||pactid||'_'||p_next_chunk,
251: lockhandle => l_lckhandle);
252: --
253: ret := dbms_lock.request(
254: lockhandle => l_lckhandle,
256: release_on_commit => TRUE);
257: if (ret <> 0) then
258: hr_utility.set_message(801,'HR_289135_NO_LOCK_GAINED');
259: hr_utility.set_message_token('LOCKNAME',
260: 'PAY_CHUNK_STATUS'||pactid||'_'||p_next_chunk);
261: hr_utility.set_message_token('LOCKERR',ret);
262: hr_utility.raise_error;
263: end if;
264: --
273: ppa.action_population_status
274: into chk_pop_status,
275: act_pop_status
276: from pay_payroll_actions ppa,
277: pay_chunk_status pcs_pop,
278: pay_chunk_status pcs_rand
279: where pcs_pop.payroll_action_id = pactid
280: and pcs_pop.chunk_number = p_next_chunk
281: and pcs_rand.payroll_action_id = pcs_pop.payroll_action_id
274: into chk_pop_status,
275: act_pop_status
276: from pay_payroll_actions ppa,
277: pay_chunk_status pcs_pop,
278: pay_chunk_status pcs_rand
279: where pcs_pop.payroll_action_id = pactid
280: and pcs_pop.chunk_number = p_next_chunk
281: and pcs_rand.payroll_action_id = pcs_pop.payroll_action_id
282: and pcs_rand.chunk_number = pcs_pop.rand_chunk_number
371: while (found = FALSE) loop
372: --
373: select max(chunk_number)
374: into next_chunk
375: from pay_chunk_status
376: where payroll_action_id = pactid
377: and population_status = 'U';
378: --
379: if (next_chunk is null) then
508: ) is
509: next_chunk number;
510: found boolean;
511: pay_pop_status pay_payroll_actions.action_population_status%type;
512: chk_pop_status pay_chunk_status.population_status%type;
513: get_paused boolean;
514: begin
515: --
516: found := FALSE;
535: begin
536: --
537: select population_status
538: into chk_pop_status
539: from pay_chunk_status
540: where payroll_action_id = pactid
541: and chunk_number = next_chunk;
542: --
543: /* Now lock the chunk for processing */
718: elsif (action_status = 'P') then
719: --
720: declare
721: got_chunk boolean;
722: chk_pop_status pay_chunk_status.population_status%type;
723: begin
724: --
725: select pcs.population_status
726: into chk_pop_status
723: begin
724: --
725: select pcs.population_status
726: into chk_pop_status
727: from pay_chunk_status pcs
728: where pcs.payroll_action_id = pactid
729: and pcs.chunk_number = next_chunk;
730: --
731: got_chunk := FALSE;
742: update pay_payroll_actions pac
743: set pac.current_chunk_number = next_chunk
744: where pac.payroll_action_id = pactid;
745: --
746: update pay_chunk_status
747: set process_status = 'P'
748: where payroll_action_id = pactid
749: and chunk_number = next_chunk;
750: --
803: while (found = FALSE) loop
804: --
805: select max(chunk_number)
806: into next_chunk
807: from pay_chunk_status
808: where payroll_action_id = pactid
809: and process_status = 'U'
810: and population_status = 'C';
811: --
819: if (next_chunk is not null) then
820: --
821: select process_status
822: into chk_status
823: from pay_chunk_status
824: where payroll_action_id = pactid
825: and chunk_number = next_chunk
826: for update of process_status;
827: --
838: elsif (pact_act_status = 'P') then
839: --
840: if (chk_status = 'U') then
841: --
842: update pay_chunk_status
843: set process_status = 'P'
844: where payroll_action_id = pactid
845: and chunk_number = next_chunk;
846:
901: next_chunk number;
902: found boolean;
903: pact_act_status pay_payroll_actions.action_status%type;
904: act_pop_status pay_payroll_actions.action_population_status%type;
905: chk_status pay_chunk_status.process_status%type;
906: begin
907: --
908: found := FALSE;
909: next_chunk := curr_chunk;
924: begin
925: --
926: select process_status
927: into chk_status
928: from pay_chunk_status
929: where payroll_action_id = pactid
930: and chunk_number = next_chunk
931: for update of process_status;
932: --
943: elsif (pact_act_status = 'P') then
944: --
945: if (chk_status = 'U') then
946: --
947: update pay_chunk_status
948: set process_status = 'P'
949: where payroll_action_id = pactid
950: and chunk_number = next_chunk;
951: --
1021: --
1022: -- Before we do any thing mark the previous chunk as complete
1023: if (curr_chunk <> 0) then
1024: --
1025: update pay_chunk_status
1026: set process_status = 'C'
1027: where payroll_action_id = pactid
1028: and chunk_number = curr_chunk;
1029: --