[Home] [Help]
6: -- Procedure VALIDATE
7: --
8: -- Populates foreign keys and validates user-provided FKs.
9: -- Determines exceptions, populates REJECT_CODE
10: -- o CARD_ID - FK to AP_CARDS. Match on CARD_NUMBER
11: -- o CODE_ID - FK to AP_CARD_CODES.
12: -- Match on CODE_VALUE for CARD_CODE_SET defined for this card program
13: -- (CARD_PROGRAM_ID)
14: -- o EMPLOYEE_ID - denormalized from AP_CARDS.
10: -- o CARD_ID - FK to AP_CARDS. Match on CARD_NUMBER
11: -- o CODE_ID - FK to AP_CARD_CODES.
12: -- Match on CODE_VALUE for CARD_CODE_SET defined for this card program
13: -- (CARD_PROGRAM_ID)
14: -- o EMPLOYEE_ID - denormalized from AP_CARDS.
15: -- o Validates Currency Codes, CARD_PROGRAM_ID
16: -- o Insures no duplicate reference numbers for this card program
17: -- o Exception raised if card profile is such that account is built from
18: -- code (BAFCF=Y) and distributions are created from line (CDF=Y) and
25: -- o EVM denotes AP_CARD_PROFILES.EMP_NOTIFICATION_LOOKUP_CODE
26: -- o MAM denotes AP_CARD_PROFILES.MGR_APPROVAL_LOOKUP_CODE
27: -- o CDF denotes AP_EXPENSE_FEED_LINES.CREATE_DISTRIBUTION_FLAG
28: -- o BAFCF denotes AP_CARD_PROFILES.BUILD_ACCT_FROM_CODE_FLAG
29: -- (AP_CARD_PROFILES is parent of AP_CARDS).
30: --
31: --
32: -- Assigned Exceptions. Exception code stored in
33: -- AP_EXPENSE_FEED_LINES.REJECT_CODE
88: from ap_expense_feed_dists efd
89: where efd.feed_line_id = efl.feed_line_id);
90:
91: --------------------------------------------------------------------
92: l_debug_info := 'Find the matching card number in AP_CARDS';
93: --------------------------------------------------------------------
94: /* update ap_expense_feed_lines efl
95: set card_id =
96: (select c.card_id
93: --------------------------------------------------------------------
94: /* update ap_expense_feed_lines efl
95: set card_id =
96: (select c.card_id
97: from ap_cards c
98: where c.card_number = efl.card_number)
99: where card_id is null
100: and reject_code = 'UNTESTED';*/
101:
111: )
112: loop
113: iby_fndcpt_setup_pub.card_exists(1.0,NULL,
114: x_return_status, x_msg_count, x_msg_data,
115: null ,trim(i.card_number), -- party id is null as we reference cards through ap_cards_all.employee_id
116: p_card_instrument, x_response);
117:
118: if (x_return_status = 'S') then
119: x_instr_id := p_card_instrument.card_id;
127: where feed_line_id=i.feed_line_id;
128: else
129: begin
130: select card_id into l_card from
131: ap_cards where card_reference_id=x_instr_id and rownum=1;
132: update ap_expense_feed_lines
133: set
134: card_id=l_card
135: where
164: --------------------------------------------------------------------
165: update ap_expense_feed_lines efl
166: set employee_id =
167: (select c.employee_id
168: from ap_cards c
169: where c.card_id = efl.card_id)
170: where card_id is not null
171: and reject_code = 'UNTESTED';
172:
242: and nvl(create_distribution_flag,'N') = 'Y'
243: and exists
244: (select 'Profile mandates building account from card code'
245: from ap_card_profiles cp,
246: ap_cards c
247: where cp.profile_id = c.profile_id
248: and c.card_id = efl.card_id
249: and nvl(cp.build_acct_from_code_flag,'N') = 'Y')
250: and reject_code = 'UNTESTED';
256: set reject_code = 'DIST REQUIRED'
257: where exists
258: (select 'Employee verification or manager approval required'
259: from ap_card_profiles cp,
260: ap_cards c
261: where cp.profile_id = c.profile_id
262: and c.card_id = efl.card_id
263: and (cp.emp_notification_lookup_code = 'Y' OR
264: cp.mgr_approval_lookup_code = 'Y'))
313: -- o EVM denotes AP_CARD_PROFILES.EMP_NOTIFICATION_LOOKUP_CODE
314: -- o MAM denotes AP_CARD_PROFILES.MGR_APPROVAL_LOOKUP_CODE
315: -- o CDF denotes AP_EXPENSE_FEED_LINES.CREATE_DISTRIBUTION_FLAG
316: -- o BAFCF denotes AP_CARD_PROFILES.BUILD_ACCT_FROM_CODE_FLAG
317: -- (AP_CARD_PROFILES is parent of AP_CARDS).
318: --
319: ---------------------------------------------------------------------------
320: PROCEDURE CREATE_DISTRIBUTIONS(
321: P_CARD_PROGRAM_ID IN NUMBER,
392: efl.description
393: from ap_expense_feed_lines efl,
394: ap_card_programs cpg,
395: ap_card_profiles cpr,
396: ap_cards c,
397: hr_employees_current_v hremp,
398: IBY_FNDCPT_PAYER_ALL_INSTRS_V IBY
399: where efl.card_id = c.card_id
400: and c.card_reference_id=IBY.instrument_id