[Home] [Help]
61: l_debug_info := 'Check if Holds Rule Setup';
62: ------------------------------------------------------------
63: select 'Y'
64: into l_is_holds_rule_setup
65: from AP_AUD_RULE_SETS rs,
66: AP_AUD_RULE_ASSIGNMENTS_ALL rsa
67: where rsa.org_id = p_org_id
68: and rsa.rule_set_id = rs.rule_set_id
69: and rs.rule_set_type = C_HOLD_RULE
685: ------------------------------------------------------------------------
686: l_debug_info VARCHAR2(200);
687:
688: l_is_holds_rule_setup VARCHAR2(1);
689: l_hold_code AP_AUD_RULE_SETS.hold_code%type;
690:
691: BEGIN
692:
693: AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start GetHoldsScenario');
696: l_debug_info := 'Check if Holds Rule Setup';
697: ------------------------------------------------------------
698: select rs.hold_code
699: into l_hold_code
700: from AP_AUD_RULE_SETS rs,
701: AP_AUD_RULE_ASSIGNMENTS_ALL rsa
702: where rsa.org_id = p_org_id
703: and rsa.rule_set_id = rs.rule_set_id
704: and rs.rule_set_type = C_HOLD_RULE
739:
740: l_org_id number;
741: l_report_submitted_date date;
742:
743: l_holds_scenario AP_AUD_RULE_SETS.hold_code%type;
744:
745: BEGIN
746:
747: AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_HOLDS_WF', 'start GetHoldsScenario');
1303: aerh.source,
1304: aerh.expense_status_code,
1305: aerh.holding_report_header_id
1306: from AP_EXPENSE_REPORT_HEADERS aerh,
1307: AP_AUD_RULE_SETS rs,
1308: AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1309: where aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
1310: and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1311: and aerh.bothpay_parent_id is null
1502: l_source ap_expense_report_headers.source%TYPE;
1503: l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1504: l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1505: l_employee_id ap_expense_report_headers.employee_id%TYPE;
1506: l_hold_rct_overdue_days ap_aud_rule_sets.hold_rct_overdue_days%TYPE;
1507:
1508:
1509: /*
1510: Criteria for this cursor is:
1523: aerh.holding_report_header_id,
1524: aerh.employee_id,
1525: rs.hold_rct_overdue_days
1526: from AP_EXPENSE_REPORT_HEADERS aerh,
1527: AP_AUD_RULE_SETS rs,
1528: AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1529: where aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
1530: and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1531: and aerh.bothpay_parent_id is null
1752: l_parent_status ap_expense_report_headers.expense_status_code%TYPE;
1753: l_source ap_expense_report_headers.source%TYPE;
1754: l_expense_status_code ap_expense_report_headers.expense_status_code%TYPE;
1755: l_holding_report_header_id ap_expense_report_headers.holding_report_header_id%TYPE;
1756: l_hold_rct_overdue_bp_cc_code ap_aud_rule_sets.hold_rct_overdue_bp_cc_code%TYPE;
1757:
1758: /*
1759: Criteria for this cursor is:
1760: - source is 'SelfService' or 'Both Pay'
1775: aerh.holding_report_header_id,
1776: rs.hold_rct_overdue_bp_cc_code
1777: from AP_EXPENSE_REPORT_HEADERS aerh,
1778: AP_EXPENSE_REPORT_HEADERS aerh2,
1779: AP_AUD_RULE_SETS rs,
1780: AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1781: where aerh.source in (AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE, AP_WEB_RECEIPTS_WF.C_BOTHPAY)
1782: and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1783: and aerh.bothpay_parent_id is not null
1978: and aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
1979: and
1980: not exists
1981: (select 1
1982: from AP_AUD_RULE_SETS rs,
1983: AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1984: where rsa.org_id = aerh.org_id
1985: and rsa.rule_set_id = rs.rule_set_id
1986: and rs.rule_set_type = C_HOLD_RULE