XLA events XLA headers , XLA lines and AP payments join

with event as (
                  select /*+ materialize */
                       xe.entity_id ,
                       sum(xl.accounted_cr) accounted_dr,
             avg(case when xe.event_type_code in ('PAYMENT CREATED','PAYMENT CANCELLED') then 1 else 0 end) ex_type,
                         max(xh.accounting_date) event_date
                  from
                       xla_events xe,
                       xla_ae_headers xh,
                       xla_ae_lines xl,
                       gl_ledgers lg
                  where /*xe.event_date <= to_date('30/04/2018', 'dd/mm/yyyy')
                        and */xh.accounting_date <= to_date('30/04/2018', 'dd/mm/yyyy')
                        and xe.event_status_code= decode(xe.event_type_code, 'PAYMENT CANCELLED', xe.event_status_code, 'P')
                        and xe.process_status_code='P'
                        and xh.event_id=xe.event_id
                        and xh.ae_header_id=xl.ae_header_id
                        and xl.accounting_class_code='CASH'
                        and xh.ledger_id=lg.ledger_id
                        and lg.ledger_category_code='PRIMARY'
                      --  and xe.entity_id=64139354
                  group by xe.entity_id
                  )
select /*+ materialize */
                   aip.invoice_id,
                 
                  xe.entity_id,
                   xe.event_id,
                   xe.event_date,
                   e.event_date,
                   aip.amount,
                   accounted_dr
 
                 
                 
              from
                    xla_events xe inner join ap_invoice_payments_all aip
                                             on xe.event_id=aip.accounting_event_id
                     
                                  inner join event e
                                             on  xe.entity_id=e.entity_id
                                           
                                       
                                           and e.event_date<=to_date('30/04/2018', 'dd/mm/yyyy')
                                  inner join ap_checks_all ca       
                                             on aip.check_id=ca.check_id
                                  left join  gl_daily_rates dr
                                             on ca.currency_code=dr.from_currency
                                             and dr.to_currency='USD'
                                             and xe.event_date=dr.conversion_date

API to update employee supervisor

DECLARE
   -- Local Variables
   -- -----------------------
   lc_dt_ud_mode           VARCHAR2(100)    := NULL;
   ln_assignment_id       NUMBER                  := 18546;
   ln_supervisor_id        NUMBER                  := 14024;
   ln_object_number       NUMBER                  := 3;
   ln_people_group_id  NUMBER                  /*:= 1*/;

   -- Out Variables for Find Date Track Mode API
   -- -----------------------------------------------------------------
   lb_correction                           BOOLEAN;
   lb_update                                 BOOLEAN;
   lb_update_override              BOOLEAN;
   lb_update_change_insert   BOOLEAN;
 
   -- Out Variables for Update Employee Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id       HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments       VARCHAR2(2000);
   ln_comment_id                             PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning        BOOLEAN;
 
   ln_special_ceiling_step_id                    PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
 lc_group_name                                          VARCHAR2(30);
 ld_effective_start_date                             PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
 ld_effective_end_date                              PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
 lb_org_now_no_manager_warning   BOOLEAN;
 lb_other_manager_warning                  BOOLEAN;
 lb_spp_delete_warning                          BOOLEAN;
 lc_entries_changed_warning                VARCHAR2(30);
 lb_tax_district_changed_warn             BOOLEAN;
 l_effective_date date;
 l_emp_hire_date date;
 l_sup_hire_date date;
 l_person_id number;



BEGIN
 
for i in (select af.assignment_id,
       af.effective_start_date,
       af.object_version_number,
       p.person_id supervisor_id,
       af.person_id
 from xxcorp_emp_sup es,
              (select distinct person_id,
                      to_number(employee_number) employee_number
                      from per_people_f) p,
             
              (select * from (
select af.person_id,
       af.effective_start_date,
       af.assignment_id,
       af.object_version_number,
       row_number() over (partition by af.person_id order by af.effective_start_date desc) row_id
from PER_ALL_ASSIGNMENTS_F af
) where row_id=1) af
where
 es.person_id=af.person_id
and es.status is null
and es.supervisor_num=p.employee_number
--and es.person_id=1236
)
loop
 
ln_assignment_id  := i.assignment_id;
   ln_supervisor_id := i.supervisor_id;
   ln_object_number  := i.object_version_number;
   l_effective_date := i.effective_start_date;

 select hire_date into l_emp_hire_date from xxbak_employees_hire_date where person_id= i.person_id;
  select hire_date into l_sup_hire_date from xxbak_employees_hire_date where person_id= i.supervisor_id;

if l_emp_hire_date < l_sup_hire_date then
  lc_dt_ud_mode := 'UPDATE';
  else
     lc_dt_ud_mode := 'CORRECTION';
     end if;
   
     if l_sup_hire_date > l_effective_date
       then l_effective_date := l_sup_hire_date;
       end if;
     

 
   if l_emp_hire_date < l_sup_hire_date then
  lc_dt_ud_mode := 'UPDATE';
  else  lc_dt_ud_mode := 'CORRECTION';
  end if;

select decode(lc_dt_ud_mode,'UPDATE',l_effective_date+1,l_effective_date) into l_effective_date from dual;


  -- Update Employee Assignment
  -- ---------------------------------------------
 l_person_id := i.person_id;
  begin
    -- DBMS_OUTPUT.put_line(lc_dt_ud_mode);
 hr_assignment_api .update_emp_asg
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                              => l_effective_date+1,
  p_datetrack_update_mode         => lc_dt_ud_mode,
  p_assignment_id                            => ln_assignment_id,
  p_supervisor_id                              => ln_supervisor_id,
  p_change_reason                           => NULL,
--  p_manager_flag                              => 'N',
  p_bargaining_unit_code              => NULL,
  p_labour_union_member_flag   => NULL,
  p_ass_attribute1 =>92,
--  p_segment1                                       => 204,
--  p_segment3                                       => 'N',
 -- p_normal_hours                              => 10,
 -- p_frequency                                       => 'W',
  -- Output data elements
  -- -------------------------------
  p_object_version_number             => ln_object_number,
  p_soft_coding_keyflex_id              => ln_soft_coding_keyflex_id,
  p_concatenated_segments             => lc_concatenated_segments,
  p_comment_id                                   => ln_comment_id,
  p_effective_start_date                      => ld_effective_start_date,
  p_effective_end_date                        => ld_effective_end_date,
  p_no_managers_warning               => lb_no_managers_warning,
  p_other_manager_warning            => lb_other_manager_warning
 );
 update xxcorp_emp_sup s set status='Y' where s.person_id=l_person_id;
 EXCEPTION
         WHEN OTHERS THEN
                    --  ROLLBACK;
                    dbms_output.put_line(l_person_id);
                     DBMS_OUTPUT.put_line(lc_dt_ud_mode);
                      dbms_output.put_line(SQLERRM);
                     
                     -- update xxcorp_emp_sup s set status= where s.person_id=l_person_id;
                   

 end;
 end loop;

 --COMMIT;


END;

How to find encumbrance, budget and actual from backend

select

sum(case when gb.actual_flag='B'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) budget,





sum(case when gb.actual_flag='A'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) actual,
sum(case when gb.actual_flag='E'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) encumb
from apps.gl_balances gb,
apps.gl_code_combinations gcc
where





gcc.CODE_COMBINATION_ID = gb.CODE_COMBINATION_ID
and gb.LEDGER_ID = 6045
and gb.PERIOD_YEAR = '2018'
and gb.PERIOD_NUM <= 3 -- March
and gb.CURRENCY_CODE = 'AZN'
and gcc.code_combination_id = 2133476

How to select PO Match Approval Level from PO_LINE_LOCATIONS_ALL

SELECT

DECODE (INSPECTION_REQUIRED_FLAG || RECEIPT_REQUIRED_FLAG,





'NN', '2-Way',

'NY', '3-Way',

'YY', '4-Way',

'Not Specified')

"Match Approval Level"






FROM PO_LINE_LOCATIONS_ALL PLL

API to delete PO

DECLARE
l_result BOOLEAN ;
l_po_header_id NUMBER ;
l_type_lookup_code VARCHAR2(100) ;
l_validation VARCHAR2(1) := 'Y' ;
cursor c is
sELECT po_header_id, pha.segment1, authorization_status , type_lookup_code, PO_HEADERS_SV3.GET_PO_STATUS(pha.PO_HEADER_ID), pha.closed_code
FROM po_headers_all pha
WHERE org_id = 93
and NVL(cancel_flag, 'N') = 'N'
and PO_HEADERS_SV3.GET_PO_STATUS(pha.PO_HEADER_ID) like '%Incomplete%'
and type_lookup_code='STANDARD';

BEGIN

for i in c loop
l_po_header_id:= i.po_header_id;
l_type_lookup_code := i.type_lookup_code;

DBMS_OUTPUT.put_line ( 'API PO_HEADERS_SV1 Call to delete PO with header :'||l_po_header_id);
l_result := po_headers_sv1.delete_po (X_po_header_id => l_po_header_id
,X_type_lookup_code => l_type_lookup_code
,p_skip_validation => l_validation);

IF l_result = TRUE THEN
COMMIT;
DBMS_OUTPUT.put_line ( 'PO with header: '||l_po_header_id||',Deleted Successfully');
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ( 'PO with header: '||l_po_header_id||',Failed to Delete');
END IF;
DBMS_OUTPUT.put_line ( 'Deletion Process Over');
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Error : '|| SQLERRM);
END;

How to get AP invoice payments using XLA events

with event as (
select /*+ materialize */
entity_id ,
max(xe.event_date) event_date
from
xla_events xe

where xe.event_date <= sysdate
and xe.event_status_code= decode(xe.event_type_code, 'PAYMENT CANCELLED', xe.event_status_code, 'P')
and xe.process_status_code='P'
group by entity_id




)
select
aip.invoice_id,
sum(aip.amount) payment_amount






from
xla_events xe inner join ap_invoice_payments_all aip
on xe.event_id=aip.accounting_event_id
inner join event e
on xe.entity_id=e.entity_id
and xe.event_date<=e.event_date
inner join ap_checks_all ca
on aip.check_id=ca.check_id


group by aip.invoice_id

How to get invoice and purchase orders (po) / relations between invoices and purchase orders (pos)

select invoice_id,
LISTAGG(po_number, '; ') WITHIN GROUP (ORDER BY po_number ) po_number,
LISTAGG(PO_creator_name, '; ') WITHIN GROUP (ORDER BY po_number ) PO_creator_name,
LISTAGG(requestor_name, '; ') WITHIN GROUP (ORDER BY po_number ) requestor_name




from(

select
ail.invoice_id,
ail.po_header_id,
pha.segment1 po_number,
he1.full_name PO_creator_name,
pr.requestor_name,
row_number() over (partition by ail.invoice_id, ail.po_header_id,pha.segment1
,he1.full_name, pr.requestor_name order by ail.invoice_id) row_id





from ap_invoice_lines_all ail inner join po_headers_all pha on ail.po_header_id=pha.po_header_id
left join fnd_user fuv1 on pha.created_by=fuv1.user_id
left join hr_employees he1 on fuv1.employee_id=he1.employee_id
left join xxcorp_po_requestor pr on pha.po_header_id=pr.po_header_id





) where row_id=1
group by invoice_id