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

How to Create Purchase Order from Backend by using interface tables

declare
CURSOR c2 (v_po_number varchar2)
        IS
        select distinct po_num, po.line_num,po.item_id, po.po_header_id,po.promised_date, po.agent_id, po.type_lookup_code,
         po.attribute12, po.attribute14, po.terms_id, po.org_id, po.rate_date,
                    po.RATE_TYPE,  po.vendor_id, po.vendor_site_id, po.currency_code, po.need_by_date, po.shipment_num,
                    remaining_qty, po.unit_price, po.SHIP_TO_ORGANIZATION_ID, line_type_id,
                    po.DISTRIBUTION_NUM,  po.code_combination_id, po.DESTINATION_CONTEXT, po.po_line_id, req_distribution_id,
                      po.item_description, unit_meas_lookup_code
                     from xxbak_open_po_v po
                   
                     where
                          remaining_qty>0
                          and po.PO_NUM=v_po_number
                   
                          ;
                   




        l_user_emp_id NUMBER;
        l_item_account_id NUMBER;
        l_location_id NUMBER;
        l_user_id NUMBER;
        l_incident_number NUMBER;
        l_org_id NUMBER;
        l_sob_id NUMBER;
        l_sob_currency VARCHAR2 (15);
        l_line_no NUMBER;
        l_request_id NUMBER;
        l_doc_num NUMBER;
         l_organization_id NUMBER;




  BEGIN
            l_user_id := fnd_profile.VALUE ('USER_ID');
            l_org_id :=fnd_profile.VALUE ('ORG_ID');
            l_organization_id :=114; -- fnd_profile.VALUE ('GAA_ORGANIZATION_ID');
            l_sob_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');   
             
         
          -- dbms_output.put_line(1);
         
     FOR h in (select distinct po_num,
                      po.po_header_id,
                      po.agent_id,
                      po.type_lookup_code,
                      po.attribute12,
                      po.attribute14,
                      po.terms_id,
                      po.org_id,
                      po.rate_date,
                      po.RATE_TYPE,
                      po.rate,
                      po.vendor_id,
                      po.vendor_site_id,
                      po.currency_code,
                      po.ship_to_location_id_h,
                      po.bill_to_location_id ,
                      po.fob_lookup_code,
                      po.note_to_authorizer,
                      po.note_to_vendor_h,
                      po.note_to_receiver,
                      po.comments,
                      po.attribute_category,
                      attribute4,
                          attribute5,
                          attribute7,
                          attribute9,
                          attribute10,
                          attribute13
                                       
                     from xxbak_open_po_v po
                      where remaining_qty>0                                 
                 
                           
                       ) loop
                             





           INSERT INTO po_headers_interface
                        (INTERFACE_HEADER_ID, --1
                          ACTION,--2
                          ORG_ID,--3
                          AGENT_ID,--4
                          DOCUMENT_TYPE_CODE, --5
                          DOCUMENT_NUM,--6
                          VENDOR_ID, --7
                          VENDOR_SITE_ID, ---8
                          LOAD_SOURCING_RULES_FLAG ,---9
                          COMMENTS, ---10
                          CURRENCY_CODE, --11
                          RATE_TYPE, --12
                          rate, ---13
                          RATE_DATE, ---14
                          attribute12, ---15 cost_center
                          attribute14, ---16  asr
                          ship_to_location_id,
                          bill_to_location_id,
                          terms_id,
                          fob,
                          note_to_vendor ,
                          note_to_receiver,
                          attribute_category,
                          attribute4,
                          attribute5,
                          attribute7,
                          attribute9,
                          attribute10,
                          attribute13
                          )
                    VALUES
                        (PO_HEADERS_INTERFACE_S.NEXTVAL, --1
                        'ORIGINAL', --2
                         h.org_id,---3
                         h.agent_id, --4
                         h.type_lookup_code, --5
                         'P-'||h.po_num, ---6
                         h.vendor_id, ---7
                         h.vendor_site_id,---8
                         'N', ----9                   
                         h.comments, --10
                         h.currency_code, --11
                         decode(h.RATE_TYPE,1000,'User',h.RATE_TYPE), --12
                         h.rate,---13
                         decode(h.CURRENCY_CODE,'AZN', null,h.rate_date), ---14
                         h.attribute12, --15
                         h.attribute14,
                         h.ship_to_location_id_h,
                         h.bill_to_location_id,
                         h.terms_id,
                         h.fob_lookup_code,
                         h.note_to_vendor_h,
                         h.note_to_receiver,
                         h.attribute_category,
                         h.attribute4,
                         h.attribute5,
                         h.attribute7,
                         h.attribute9,
                         h.attribute10,
                         h.attribute13
                         ); 




         for l in c2(h.po_num) loop   
         
                    INSERT INTO po_lines_interface
                    (interface_line_id, ---1
                    interface_header_id, ---2
                    line_type_id,---3
                    item_id, ---4
                    item_description,  ---5
                    unit_price, ---6
                    unit_of_measure, ---7
                    quantity,---8
                    note_to_vendor, ---9
                    shipment_num, ----10
                    line_num, ---11
                    NEED_BY_DATE,  ---12
                    PROMISED_DATE, ---13
                    SHIPMENT_TYPE, ---14
                    ship_to_organization_id)  ---15 
                    VALUES
                    ( PO_LINES_INTERFACE_S.NEXTVAL,---1
                     PO_HEADERS_INTERFACE_S.CURRVAL, ---2
                     l.line_type_id,---3
                     l.item_id, ---4
                     l.item_description, ---5
                     l.unit_price, ---6
                     l.unit_meas_lookup_code, ---7
                     l.remaining_qty, ---8
                     l.item_description,---9
                     l.shipment_num, ---10
                     l.line_num, ---11
                     l.need_by_date,---12
                     l.promised_date,---13
                     'STANDARD',---14
                     l.SHIP_TO_ORGANIZATION_ID);
                   
                   INSERT INTO po_distributions_interface
                        (interface_line_id,---1
                        interface_header_id, ---2
                        interface_distribution_id, ---3
                        ORG_ID, ---4
                        QUANTITY_ORDERED, ---5
                        DESTINATION_TYPE_CODE, ---6
                        CHARGE_ACCOUNT_ID, -----7
                        DISTRIBUTION_NUM, -----8
                        DESTINATION_ORGANIZATION_ID, --9
                        req_distribution_id)
                    VALUES
                        (PO_LINES_INTERFACE_S.CURRVAL, ---1
                         PO_HEADERS_INTERFACE_S.CURRVAL, ---2
                         PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,----3
                         l.ORG_ID,  ---4
                         l.REMAINING_QTY, ----5
                         l.DESTINATION_CONTEXT, ----6
                         l.code_combination_id, ----7
                         l.DISTRIBUTION_NUM, ---8
                         l.SHIP_TO_ORGANIZATION_ID,
                         l.req_distribution_id);
                       
                     
             end loop;
     
   

    END LOOP;
    Commit;

                END;

How to Cancel PO line and Return PR and add PR to same PO?

Purchase Orders is Approved and it is necessary:

• Cancel PO line
• Return PR line
• Make necessary modifications in PR and submit second time for approval
• Add changed PR line to the same PO

1. Search for ‘Approved’ Purchase Order:

Note: You can search PO by its lines and cancel PO line without opening PO. Or you can search PO
by header, go inside PO,  stand on the line→ Tools→ Control→ in order to cancel PO line.

The difference of doing this is when you search PO by its lines and cancel line without opening it
then status of PO will remain as ‘Approved’. (in case if you need to keep the Approved status if you
have a lot of lines and do not want to submit PO to approval second time)

But when you search PO by its header and go inside PO and cancel line, then status of PO automatically
will change to ‘Requires Reapproval’.

Select the line which is going to be cancelled→ Tools→ Control→

Cancel PO Line:

After Cancel Line search PO to check its status. Status of PO has not been changed: “Approved’

From Purchasing Super User Navigator go to Autocreate in order to see cancelled PO Line which has been
returned to Autocreate:

Search by entering PR number which was related to your PO:

PR Line is shown in Autocreate. Select the line and go to Tools→ Return Requisition

Enter reason for Return and press Ok:

Check PR status: “Return”. Now you will be able to make necessary changes.
Note: When PR is in “Approved” status it is not possible to Edit PR.

Send to approval after necessary changes:

Now go to Autocreate search for your PR.  Tick PR line and in ‘Action’ box below choose in order to be able to
add to existing PO, then choose Manual.

Note: If you want to create completely new PO from the same PR then you need to select ‘Create’ from Action
box.

Click ‘Add to Document’ and ‘Create’

PO is created with details. You need just to submit for approval.