[Home] [Help]
9207: | to PSA tables as shown below: |
9208: | |
9209: | SLA Table PSA Table |
9210: | ======== ======== |
9211: | xla_events_gt psa_xla_events_logs |
9212: | xla_validation_lines_gt psa_xla_validation_lines_logs |
9213: | xla_ae_lines_gt psa_xla_ae_lines_logs |
9214: | xla_ae_headers_gt psa_xla_ae_headers_logs |
9215: | |
9289: budgetary_control_flag,
9290: phase,
9291: sysdate
9292: BULK COLLECT INTO l_xla_events
9293: FROM xla_events_gt ;
9294:
9295:
9296: SELECT event_id,
9297: entity_id,
9458: phase,
9459: sysdate
9460: BULK COLLECT INTO l_xla_distribution_links
9461: FROM xla_distribution_links
9462: where event_id IN (SELECT event_id from psa_bc_xla_events_gt)
9463: and application_id = psa_bc_xla_pvt.g_application_id;
9464:
9465:
9466:
9538: SELECT event_id, ae_header_id, ae_line_num, period_name,
9539: accounting_entry_status_code, balancing_line_type
9540: FROM xla_validation_lines_gt;
9541:
9542: CURSOR debug_xla_events_gt IS
9543: SELECT application_id, event_id, event_date, event_type_code,
9544: reference_num_1
9545: FROM xla_events_gt;
9546:
9541:
9542: CURSOR debug_xla_events_gt IS
9543: SELECT application_id, event_id, event_date, event_type_code,
9544: reference_num_1
9545: FROM xla_events_gt;
9546:
9547: CURSOR debug_xla_psa_bc_v IS
9548: SELECT event_id, ae_header_id, ae_line_num, entity_id, ledger_id,
9549: period_name
9553: SELECT hierarchy_id, ae_header_id, ae_line_num, event_id,
9554: status_code
9555: FROM psa_bc_alloc_gt;
9556:
9557: -- Check whether all the events from XLA_EVENTS_GT have come to
9558: -- XLA_AE_LINES_GT.
9559: /* If any event is missed we will treat it as
9560: FATAL error because there was some setup problem in SLA. */
9561: CURSOR c_cnt_events IS
9559: /* If any event is missed we will treat it as
9560: FATAL error because there was some setup problem in SLA. */
9561: CURSOR c_cnt_events IS
9562: SELECT (SELECT COUNT (*)
9563: FROM xla_events_gt) event_count,
9564: (SELECT COUNT (DISTINCT (event_id))
9565: FROM xla_ae_lines_gt) ae_event_count
9566: FROM DUAL;
9567:
9881: -- PSA regular tables.
9882:
9883: debug_xla ( 'BUDGETARY_CONTROL_START' );
9884:
9885: -- Check whether all the events in XLA_EVENTS_GT
9886: -- are available in XLA_AE_LINES_GT.
9887: /* If some events are missing that means that there is some issue in SLA
9888: setup for the event, that's why SLA didn't put that event to be
9889: considered for accounting. In this case Funds Checker API
9893: CLOSE c_cnt_events;
9894:
9895: -- ====== FND LOG ======
9896: psa_utils.debug_other_string(g_error_level,l_full_path,
9897: ' BCTRL -> Count of events in XLA_EVENTS_GT: '||l_event_cnt);
9898: psa_utils.debug_other_string(g_error_level,l_full_path,
9899: ' BCTRL -> Count of events in XLA_AE_LINES_GT: '||l_ae_event_cnt);
9900: -- ====== FND LOG ======
9901:
9902: fnd_file.put_line(fnd_file.log,'The following are the invalid accounting errrors');
9903: fnd_file.put_line(fnd_file.log,'=============================================== ');
9904: for acc_error in ( select document_reference , encoded_message
9905: from psa_bc_accounting_errors b
9906: where event_id in (select event_id from xla_events_gt))
9907: loop
9908: fnd_file.put_line(fnd_file.log , ' document_referece ' || acc_error.document_reference);
9909: fnd_file.put_line(fnd_file.log , acc_error.encoded_message);
9910: End loop;
9919: fnd_file.put_line(fnd_file.log,'The following entities have not been processed');
9920: fnd_file.put_line(fnd_file.log,'======================================= ');
9921: for missing_entity in
9922: ( select e.entity_id,g.event_id,g.source_id_int_2,e.transaction_number
9923: from xla_transaction_entities_upg e , xla_events_gt g
9924: where g.entity_id = e.entity_id
9925: and g.event_id not in (select event_id from xla_ae_lines_gt) )
9926: loop
9927: fnd_file.put_line(fnd_file.log , ' BC_Event_id ' || missing_entity.event_id || ' Transaction Number ' || missing_entity.transaction_number || ' Distribution id ' || missing_entity.source_id_int_2 );
10475: END LOOP;
10476:
10477: -- Bug 5397349 .. End
10478:
10479: -- Update PSA_BC_XLA_EVENTS_GT table with result_code
10480:
10481: -- ====== FND LOG ======
10482: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Update result_code '||
10483: 'of psa_bc_xla_events_gt');
10479: -- Update PSA_BC_XLA_EVENTS_GT table with result_code
10480:
10481: -- ====== FND LOG ======
10482: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Update result_code '||
10483: 'of psa_bc_xla_events_gt');
10484: -- ====== FND LOG ======
10485:
10486: -- this update will ensure that only valid xla events
10487: -- provided for FUNDS CHECK (meant xla_ae_headers rows updated with funds_status_code)
10485:
10486: -- this update will ensure that only valid xla events
10487: -- provided for FUNDS CHECK (meant xla_ae_headers rows updated with funds_status_code)
10488: -- are updated with appropiate status. Events NOT picked by funds checker will be updated with XLA_ERROR.
10489: UPDATE psa_bc_xla_events_gt eg
10490: SET result_code = (SELECT decode(min(funds_status_code),
10491: 'T', 'FATAL',
10492: 'S', 'SUCCESS',
10493: 'A', 'ADVISORY',
10731: -- ====== FND LOG ======
10732: END LOOP;
10733:
10734: -- ====== FND LOG ======
10735: psa_utils.debug_other_string(g_error_level,l_full_path, ' BCTRL -> XLA_EVENTS_GT DUMP');
10736: psa_utils.debug_other_string(g_error_level,l_full_path, ' ---------------------------- ');
10737: -- ====== FND LOG ======
10738: FOR z in debug_xla_events_gt
10739: LOOP
10734: -- ====== FND LOG ======
10735: psa_utils.debug_other_string(g_error_level,l_full_path, ' BCTRL -> XLA_EVENTS_GT DUMP');
10736: psa_utils.debug_other_string(g_error_level,l_full_path, ' ---------------------------- ');
10737: -- ====== FND LOG ======
10738: FOR z in debug_xla_events_gt
10739: LOOP
10740: -- ====== FND LOG ======
10741: psa_utils.debug_other_string(g_error_level,l_full_path, ' APPLICATION_ID = '||z.application_id||' , '||
10742: ' EVENT_ID = '||z.event_id||' , '||
10812: CURSOR c_success_evt_exists IS
10813: SELECT 'Successful event exists in the current packet'
10814: FROM gl_bc_packets
10815: WHERE event_id IN (SELECT event_id
10816: FROM psa_bc_xla_events_gt
10817: )
10818: AND application_id = PSA_BC_XLA_PVT.g_application_id
10819: AND status_code = 'A';
10820:
10883: -- back to the calling transaction.
10884:
10885: IF (l_f81_cnt <> 0 OR l_f82_cnt <> 0) THEN
10886: FORALL k IN 1..p_failed_evnt_array.COUNT
10887: UPDATE psa_bc_xla_events_gt
10888: SET result_code = 'XLA_ERROR'
10889: WHERE event_id = p_failed_evnt_array(k)
10890: AND result_code <> 'XLA_ERROR';
10891:
10889: WHERE event_id = p_failed_evnt_array(k)
10890: AND result_code <> 'XLA_ERROR';
10891:
10892: -- ====== FND LOG ======
10893: psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated PSA_BC_XLA_EVENTS_GT '||sql%rowcount||
10894: ' rows to XLA_ERROR status.');
10895: -- ====== FND LOG ======
10896:
10897: END IF;
10913: WHERE affect_funds_flag = 'Y'
10914: AND packet_id IN ( SELECT packet_id
10915: FROM gl_bc_packets bc
10916: WHERE event_id IN ( SELECT event_id
10917: FROM psa_bc_xla_events_gt
10918: )
10919: AND application_id = PSA_BC_XLA_PVT.g_application_id
10920: AND result_code IN ('F81', 'F82')
10921: ) ;