[Home] [Help]
398: AND a.assignment_id = l.assignment_id);
399:
400: CURSOR c_chk_empty_requests IS
401: SELECT 1
402: FROM per_letter_requests r
403: WHERE NOT EXISTS
404: (SELECT NULL
405: FROM per_letter_request_lines L
406: WHERE r.letter_request_id = l.letter_request_id);
432: FETCH c_chk_empty_requests INTO v_empty_requests;
433: IF c_chk_empty_requests%FOUND THEN
434: CLOSE c_chk_empty_requests;
435: --
436: DELETE FROM per_letter_requests R
437: WHERE r.business_group_id = p_business_group_id
438: AND r.request_status = 'PENDING'
439: AND r.auto_or_manual = 'AUTO'
440: AND NOT EXISTS
480:
481: CURSOR csr_let_req IS
482: SELECT R.LETTER_REQUEST_ID,
483: r.letter_type_id
484: FROM PER_LETTER_REQUESTS R,
485: PER_LETTER_GEN_STATUSES s
486: WHERE r.business_group_id + 0 = p_business_group_id
487: AND s.business_group_id + 0 = p_business_group_id
488: AND r.LETTER_TYPE_ID = S.LETTER_TYPE_ID
498: AND s.assignment_status_type_id = p_assignment_status_type_id
499: AND s.enabled_flag = 'Y'
500: AND NOT EXISTS
501: (SELECT NULL
502: FROM per_letter_requests r
503: WHERE r.letter_type_id = S.letter_type_id
504: AND R.business_group_id + 0 = P_business_group_id
505: AND r.request_status = 'PENDING'
506: AND r.auto_or_manual = 'AUTO');
511: WHERE s.business_group_id + 0 = p_business_group_id
512: AND s.assignment_status_type_id = p_assignment_status_type_id
513: AND s.enabled_flag = 'Y'
514: AND s.letter_type_id NOT IN (SELECT distinct(r.letter_type_id)
515: FROM per_letter_requests r
516: WHERE r.business_group_id + 0
517: = p_business_group_id
518: and r.REQUEST_STATUS
519: = 'PENDING'
534: -- Cursor added for bug 3680947.
535: --
536: CURSOR csr_check_manual_or_auto IS
537: SELECT 1
538: FROM PER_LETTER_REQUESTS PLR,
539: PER_LETTER_GEN_STATUSES PLGS
540: WHERE PLGS.business_group_id + 0 = p_business_group_id
541: AND PLR.business_group_id +0 = p_business_group_id
542: AND PLGS.assignment_status_type_id = p_assignment_status_type_id
601: , p_last_updated_by
602: , p_last_update_login
603: , p_last_updated_by
604: , trunc(SYSDATE)
605: FROM PER_LETTER_REQUESTS r
606: , PER_LETTER_GEN_STATUSES s
607: , PER_ASSIGNMENTS a
608: WHERE R.LETTER_TYPE_ID = S.LETTER_TYPE_ID
609: AND R.LETTER_TYPE_ID = v_letter_type
640: OPEN csr_letter_type;
641: LOOP
642: FETCH csr_letter_type into v_letter_type;
643: EXIT WHEN csr_letter_type%NOTFOUND;
644: insert into PER_LETTER_REQUESTS(
645: LETTER_REQUEST_ID
646: , BUSINESS_GROUP_ID
647: , LETTER_TYPE_ID
648: , DATE_FROM
652: , LAST_UPDATED_BY
653: , LAST_UPDATE_LOGIN
654: , CREATED_BY
655: , CREATION_DATE)
656: select PER_LETTER_REQUESTS_S.nextval
657: , P_Business_group_id
658: , v_letter_type
659: , P_session_date
660: , 'PENDING'
698: , p_last_updated_by
699: , p_last_update_login
700: , p_last_updated_by
701: , trunc(SYSDATE)
702: FROM PER_LETTER_REQUESTS R
703: , PER_LETTER_GEN_STATUSES s
704: WHERE R.LETTER_TYPE_ID = S.LETTER_TYPE_ID
705: AND p_assignment_status_type_id = S.ASSIGNMENT_STATUS_TYPE_ID
706: AND S.business_group_id + 0 = R.business_group_id + 0
769: AND a.application_id = p_application_id;
770:
771: CURSOR csr_let_req_id IS
772: SELECT r.letter_request_id
773: FROM PER_LETTER_REQUESTS R,
774: PER_LETTER_GEN_STATUSES s,
775: PER_ASSIGNMENT_STATUS_TYPES T
776: WHERE r.business_group_id + 0 = p_business_group_id
777: AND s.business_group_id + 0 = p_business_group_id
783: AND r.auto_or_manual = 'AUTO';
784:
785: CURSOR csr_odd_reqs IS
786: SELECT R.LETTER_REQUEST_ID
787: FROM PER_LETTER_REQUESTS R,
788: PER_LETTER_GEN_STATUSES s,
789: PER_ASSIGNMENT_STATUS_TYPES T
790: WHERE r.business_group_id + 0 = p_business_group_id
791: AND s.business_group_id + 0 = p_business_group_id
844: IF csr_odd_reqs%FOUND THEN
845: CLOSE csr_odd_reqs;
846: --
847: FOR csr_odd_reqs_rec IN csr_odd_reqs LOOP
848: DELETE FROM per_letter_requests R
849: WHERE r.letter_request_id =
850: csr_odd_reqs_REC.letter_request_id
851: AND r.business_group_id + 0 = p_business_group_id;
852: END LOOP;