What are the disposition status in Quality sample

Issue:

What are the different values for the field - Disposition - in the gmd_sampling_events table 

Solution:

The values are -

0PL - Planned
0RT - Retain
1P - Pending
2I - In Progress
3C - Complete
4A - Accept
5AV - Accept with Variance
6RJ - Reject
7CN - Cancel

Issue:

What table stores the correct sample disposition and sample group disposition?

Query raised due to sample disposition in gmd_samples does not seem accurate?

Solution:

The sample disposition in gmd_samples should not be used and is not accurate.

To get the correct value for sample disposition, the relevant fields are:

For a sample group:       disposition in table gmd_event_spec_disp
For a sample:                 disposition in table gmd_sample_spec_disp

AP Supplier query with Bank and Tax details (GST & VAT)

 

select

 (SELECT hou.NAME

  FROM apps.hr_operating_units hou

  WHERE 1 = 1

  AND hou.organization_id = aps.org_id) ou_name,

  (SELECT

    paym.payment_method_code

FROM APPS.iby_ext_party_pmt_mthds paym

WHERE IEPA.ext_payee_id = paym.ext_pmt_party_id

    and aps.vendor_site_id = iepa.supplier_site_id

    --AND    ass.supplier_site_id IS NULL

    AND    paym.primary_flag = 'Y' and rownum=1

    )SITE_PAYMENT_METHOD,

aps.ORG_ID,

ass.segment1 SUPPLIER_NUMBER,

NULL "LEGACY_SUPPLIER_NUMBER",

ass.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,

ass.VENDOR_NAME SUPPLIER_NAME,

ass.VENDOR_NAME_ALT SUPPLIER_NAME_ALT,

ass.num_1099 TAXPAYER_ID,

ass.vat_registration_num TAX_REGISTRATION_NUM,

ass.allow_awt_flag SUPPLIER_ALLOW_WITHHOLDING_TAX,

ASS.CREATION_DATE SUPPLIER_CREATION_DATE,

(select user_name from apps.fnd_user where user_id=ASS.CREATED_BY) SUPPLIER_CREATED_BY,

ASS.LAST_UPDATE_DATE SUPPLIER_LAST_UPDATE_DATE,

decode(ass.END_DATE_ACTIVE,null,'ACTIVE','IN ACTIVE') "Supplier active code ",

(select user_name from apps.fnd_user where user_id=ass.LAST_UPDATED_BY) SUPPLIER_LAST_UPDATED_BY,

aps.VENDOR_SITE_ID,

aps.INACTIVE_DATE,

aps.vendor_site_code SUPPLIER_SITE_CODE,

aps.vendor_SITE_CODE_ALT SITE_CODE_ALT,

aps.allow_awt_flag SITE_ALLOW_WITHHOLDING_TAX

,APS.ADDRESS_LINE1

,APS.ADDRESS_LINE2

,APS.ADDRESS_LINE3

,APS.ADDRESS_LINE4

,APS.CITY

,APS.STATE

,APS.ZIP POST_CODE

,APS.CREATION_DATE SUPPLIER_SITE_CREATION_DATE

,APS.CREATED_BY SUPPLIER_SITE_CREATED_BY

,APS.LAST_UPDATE_DATE SUPPLIER_SITE_LAST_UPDATE_DATE

,APS.LAST_UPDATED_BY SUPPLIER_SITE_LAST_UPDATED_BY

,aps.SUPPLIER_NOTIF_METHOD SITE_PO_NOTIF_METHOD

,APS.EMAIL_ADDRESS SITE_PO_EMAIL

,Aps.PAY_GROUP_LOOKUP_CODE

,IEPA.OBJECT_VERSION_NUMBER MAX_OBJECT_VERSION

--,ieppm.payment_method_code

,iepa.DEFAULT_PAYMENT_METHOD_CODE SITE_PAYMENT_METHOD

,t.name SITE_TERMS_NAME

,IEPA.REMIT_ADVICE_DELIVERY_METHOD SITE_REMIT_ADVICE_DEL_METHOD

,iepa.remit_advice_email SITE_REMIT_ADVICE_EMAIL

--,accts.country_code BANK_COUNTRY_CODE

,decode(accts.country_code,'IN','IN','NULL')COUNTRY_CODE

,NULL "Allow_International_Payments"

,branch.bank_name "bank_name"

,branch.bank_branch_name BANK_BRANCH_NAME

,branch.branch_number bank_branch_name

,accts.bank_account_name BANK_ACCOUNT_NAME

,accts.bank_account_num BANK_ACCOUNT_NUMBER

,accts.check_digits "check_digits"

,NULL BIC

,accts.iban "IBAN"

,uses.start_date "Account_start_date"

,uses.end_date "Account_end_date"

,accts.currency_code "Account_Currency_Code"

,accts.CREATION_DATE BANK_ACCOUNT_CREATION_DATE

,accts.LAST_UPDATE_DATE BANK_ACCOUNT_LAST_UPDATE_DATE

,accts.secondary_account_reference "SECONDARY_ACCOUNT_REFERENCE",

aps.attribute1 po_flag

  FROM apps.ap_supplier_sites_all aps,

  apps.ap_suppliers ass,

  apps.ap_terms t,

  apps.iby_external_payees_all iepa, --bank 2

apps.iby_pmt_instr_uses_all uses, --bank 3

apps.iby_account_owners owners,--bank4

apps.iby_ext_bank_accounts accts,--bank5

apps.ce_bank_branches_v branch--bank6

WHERE 1=1-- Aps.ORG_ID=2704

--and owners.primary_flag      = 'Y'

--and ass.segment1 IN ('342255','345467')--'346873'

AND ass.end_date_active  IS NULL

AND aps.inactive_date    IS NULL

and aps.terms_id=t.term_id

--and owners.account_owner_party_id is not null

--and ieb.country_code='IN'

AND owners.ext_bank_account_id = accts.ext_bank_account_id(+)

AND owners.ext_bank_account_id(+) = uses.instrument_id

AND iepa.ext_payee_id       = uses.ext_pmt_party_id(+)

AND iepa.payee_party_id  = owners.account_owner_party_id

--AND owners.PRIMARY_FLAG IN ('Y','N')

and uses.end_date is null

AND iepa.supplier_site_id    = aps.vendor_site_id

AND ass.vendor_id              = aps.vendor_id

AND branch.branch_party_id(+)    = accts.branch_id

AND uses.instrument_type   = 'BANKACCOUNT'

AND iepa.payment_function in('PAYABLES_DISB')

GROUP BY

aps.ORG_ID,

ass.segment1,

ass.VENDOR_TYPE_LOOKUP_CODE,

ass.VENDOR_NAME,

ass.VENDOR_NAME_ALT,

ass.num_1099,

ass.vat_registration_num ,

ass.allow_awt_flag ,

ASS.CREATION_DATE ,

ASS.CREATED_BY ,

ASS.LAST_UPDATE_DATE ,

decode(ass.END_DATE_ACTIVE,null,'ACTIVE','IN ACTIVE'),

ass.LAST_UPDATED_BY,

aps.VENDOR_SITE_ID,

aps.INACTIVE_DATE,

aps.vendor_site_code ,

aps.vendor_SITE_CODE_ALT ,

aps.allow_awt_flag

,APS.ADDRESS_LINE1

,APS.ADDRESS_LINE2

,APS.ADDRESS_LINE3

,APS.ADDRESS_LINE4

,APS.CITY

,APS.STATE

--,payees.DEFAULT_PAYMENT_METHOD_CODE

,iepa.DEFAULT_PAYMENT_METHOD_CODE--,ieppm.payment_method_code

,APS.ZIP

,APS.CREATION_DATE

,APS.CREATED_BY

,APS.LAST_UPDATE_DATE

,APS.LAST_UPDATED_BY

,aps.SUPPLIER_NOTIF_METHOD

,APS.EMAIL_ADDRESS

,ApS.PAY_GROUP_LOOKUP_CODE

,IEPA.OBJECT_VERSION_NUMBER

--,ieppm.payment_method_code SITE_PAYMENT_METHOD

,t.name

,IEPA.REMIT_ADVICE_DELIVERY_METHOD

,iepa.remit_advice_email

,accts.country_code

--,NULL "Allow_International_Payments"

--,bank.party_name

,branch.bank_name

,branch.bank_branch_name

,branch.branch_number

,accts.bank_account_name

,accts.bank_account_num

,accts.check_digits

--,NULL BIC

,accts.iban

,uses.start_date

,uses.end_date

,accts.currency_code

,accts.CREATION_DATE

,accts.LAST_UPDATE_DATE

,accts.secondary_account_reference

,aps.attribute1

--,GST_NUMBER

--,PAN_NUMBER

,ASS.VENDOR_ID

,IEPA.ext_payee_id

,ass.LAST_UPDATED_BY

--,paym.ext_pmt_party_id

, aps.vendor_site_id

, iepa.supplier_site_id

---order by ORG_ID

UNION

select

 (SELECT hou.NAME

  FROM apps.hr_operating_units hou

  WHERE 1                 = 1

  AND hou.organization_id = aps.org_id

  ) ou_name,

(SELECT

    paym.payment_method_code

FROM

    apps.iby_external_payees_all payee,

    apps.iby_ext_party_pmt_mthds paym

WHERE

    payee.ext_payee_id = paym.ext_pmt_party_id

    and aps.vendor_site_id = payee.supplier_site_id

    --AND    ass.supplier_site_id IS NULL

    AND    paym.primary_flag = 'Y' and rownum=1

    )SITE_PAYMENT_METHOD,

aps.ORG_ID,ass.segment1 SUPPLIER_NUMBER,

NULL "LEGACY_SUPPLIER_NUMBER",

ass.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,

ass.VENDOR_NAME SUPPLIER_NAME,

ass.VENDOR_NAME_ALT SUPPLIER_NAME_ALT,

ass.num_1099 TAXPAYER_ID,

ass.vat_registration_num TAX_REGISTRATION_NUM,

ass.allow_awt_flag SUPPLIER_ALLOW_WITHHOLDING_TAX,

ASS.CREATION_DATE SUPPLIER_CREATION_DATE,

(select user_name from apps.fnd_user where user_id=ASS.CREATED_BY) SUPPLIER_CREATED_BY,

ASS.LAST_UPDATE_DATE SUPPLIER_LAST_UPDATE_DATE,

decode(ass.END_DATE_ACTIVE,null,'ACTIVE','IN ACTIVE') "Supplier active code ",

(select user_name from apps.fnd_user where user_id=ass.LAST_UPDATED_BY) SUPPLIER_LAST_UPDATED_BY,

aps.VENDOR_SITE_ID,

aps.INACTIVE_DATE,

aps.vendor_site_code SUPPLIER_SITE_CODE,

aps.vendor_SITE_CODE_ALT SITE_CODE_ALT,

aps.allow_awt_flag SITE_ALLOW_WITHHOLDING_TAX

,APS.ADDRESS_LINE1

,APS.ADDRESS_LINE2

,APS.ADDRESS_LINE3

,APS.ADDRESS_LINE4

,APS.CITY

,APS.STATE

,APS.ZIP POST_CODE

,APS.CREATION_DATE SUPPLIER_SITE_CREATION_DATE

,APS.CREATED_BY SUPPLIER_SITE_CREATED_BY

,APS.LAST_UPDATE_DATE SUPPLIER_SITE_LAST_UPDATE_DATE

,APS.LAST_UPDATED_BY SUPPLIER_SITE_LAST_UPDATED_BY

,aps.SUPPLIER_NOTIF_METHOD SITE_PO_NOTIF_METHOD

,APS.EMAIL_ADDRESS SITE_PO_EMAIL

,ApS.PAY_GROUP_LOOKUP_CODE

,NULL MAX_OBJECT_VERSION

,NULL SITE_PAYMENT_METHOD

--,NULL SITE_PAYMENT_METHOD1

,t.name SITE_TERMS_NAME

,NULL SITE_REMIT_ADVICE_DEL_METHOD

,NULL SITE_REMIT_ADVICE_EMAIL

--,accts.country_code BANK_COUNTRY_CODE

,NULL COUNTRY_CODE

,NULL "Allow_International_Payments"

,NULL "bank_name"

,NULL BANK_BRANCH_NAME

,NULL bank_branch_name

,NULL BANK_ACCOUNT_NAME

,NULL BANK_ACCOUNT_NUMBER

,NULL "check_digits"

,NULL BIC

,NULL "IBAN"

,NULL "Account_start_date"

,NULL "Account_end_date"

,NULL "Account_Currency_Code"

,NULL BANK_ACCOUNT_CREATION_DATE

,NULL BANK_ACCOUNT_LAST_UPDATE_DATE

,NULL "SECONDARY_ACCOUNT_REFERENCE",

aps.attribute1 po_flag

from

apps.ap_suppliers ass,

apps.ap_supplier_sites_all aps,

apps.ap_terms t

where ass.vendor_id=aps.vendor_id

--and ass.segment1 IN ('342255','345467')

and ass.end_date_active IS NULL

AND aps.inactive_date IS NULL

AND aps.pay_site_flag = 'Y'

and aps.terms_id=t.term_id(+);

Invoice posted register Query

 select aia.INVOICE_NUM

,aba.batch_name

,aia.INVOICE_AMOUNT

,aia.PAY_GROUP_LOOKUP_CODE INv_group

,aia.INVOICE_CURRENCY_CODE

,aia.CREATION_DATE

,To_char(aia.creation_date,'MONTH') InvCreation_Month

,aia.INVOICE_DATE

,aia.SOURCE

,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE

,PAYMENT_METHOD_CODE INVOICE_PAY_METHOD

,aia.amount_paid amount_paid

,aia.description

,aia.doc_sequence_value VOUCHER_NUM

,(select max(aila.ACCOUNTING_DATE)

        from apps.ap_invoice_lines_all aila            

        where aila.invoice_id = aia.invoice_id

        and aila.org_id = aia.org_id

        and rownum =1) ACCOUNTING_DATE

,aia.GL_DATE

,asu.VENDOR_NAME

,asu.segment1 VENDOR_NUMBER

,assa.VENDOR_SITE_CODE

,(select aca.CHECK_NUMBER 

    from apps.ap_invoice_payments_all aipa,

         apps.ap_checks_all aca 

    where  aipa.check_id = aca.check_id 

           and nvl(aipa.reversal_flag,'N') <> 'Y'

           and  aipa.invoice_id = aia.invoice_id

           and aipa.org_id = aia.org_id

           and rownum = 1) CHECK_NUMBER

,(select aca.CHECK_DATE 

    from apps.ap_invoice_payments_all aipa,

         apps.ap_checks_all aca 

    where  aipa.check_id = aca.check_id 

           and nvl(aipa.reversal_flag,'N') <> 'Y'

           and  aipa.invoice_id = aia.invoice_id

           and aipa.org_id = aia.org_id

           and rownum = 1) CHECK_DATE

,(select distinct poh.segment1

    from apps.po_headers_all poh,

         apps.ap_invoice_lines_all aila

    where aila.po_header_id = poh.po_header_id

      and aila.invoice_id = aia.invoice_id

      and poh.org_id = aia.org_id

      and rownum = 1) PO_NUMBER

,at.name TERMS

,Decode((select distinct poh.segment1

    from apps.po_headers_all poh,

         apps.ap_invoice_lines_all aila

    where aila.po_header_id = poh.po_header_id

      and aila.invoice_id = aia.invoice_id

      and poh.org_id = aia.org_id

      and rownum = 1),null,'N','Y') "PO Y/N"

 from    apps.ap_invoices_all aia, 

         apps.ap_batches_all aba,

         apps.ap_suppliers asu,

         apps.ap_supplier_sites_all assa,

         apps.ap_terms at

        -- apps.ap_invoice_payments_all aipa,

        -- apps.ap_checks_all aca

where aia.batch_id = aba.batch_id

and asu.vendor_id = assa.vendor_id

and aia.vendor_id  = asu.vendor_id

and aia.vendor_site_id = assa.vendor_site_id

and aia.terms_id = at.term_id

--and aia.invoice_id = aipa.invoice_id

--and aipa.check_id = aca.check_id

and aia.gl_date between '01-JAN-2020' and '31-MAR-2020'

order by aia.invoice_date;

Customer Bill To Role update API

 SELECT * FROM hz_cust_account_roles;

   set serveroutput on;

DECLARE

  

  l_contact_point_rec   HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE;

  l_role_id NUMBER := 0;

  l_cust_acct_id NUMBER := 0;

  l_obj_num            NUMBER := 0;

  p_party_id           HZ_PARTIES.party_id % TYPE;

  x_msg_count          NUMBER;

  x_msg_data           VARCHAR2(2000) := NULL;

  x_return_status      VARCHAR2(1000) := NULL;

  

  CURSOR C1 IS SELECT ACCOUNT_NUMBER, BILL_TO_SITE, STATUS, UPD_STATUS

                fROM xx_bill_to_roll_tbl WHERE NVL(UPD_STATUS,'I') = 'I';

  

BEGIN

FOR I IN  C1 LOOP

SELECT DISTINCT role_acct.CUST_ACCOUNT_ID, acct_role.CUST_ACCOUNT_ROLE_ID

                INTO l_cust_acct_id, l_role_id

                FROM apps.hz_contact_points cont_point,

               apps.hz_cust_account_roles acct_role,

               apps.hz_parties party,

               apps.hz_parties rel_party,

               apps.hz_relationships rel,

               apps.hz_org_contacts org_cont,

               apps.hz_cust_accounts role_acct,

               apps.hz_contact_restrictions cont_res,

               apps.hz_person_language per_lang,

               apps.hz_cust_acct_sites_all hcasa,

               apps.hz_cust_site_uses_all hcu,

               apps.hz_role_responsibility resp,

               apps.hz_party_sites hps

        WHERE  acct_role.party_id = rel.party_id

          AND  acct_role.role_type = 'CONTACT'

          AND  org_cont.party_relationship_id = rel.relationship_id

          AND  hcasa.party_site_id = hps.party_site_id

          AND  rel.subject_id = party.party_id

          AND  rel_party.party_id = rel.party_id

          AND  cont_point.owner_table_id(+) = rel_party.party_id

          AND  cont_point.contact_point_type = 'EMAIL'

          AND cont_point.status='A' 

          AND  acct_role.cust_account_id = role_acct.cust_account_id

          AND  role_acct.party_id = rel.object_id

          AND  party.party_id = per_lang.party_id(+)

          AND  per_lang.native_language(+) = 'Y'

          AND  party.party_id = cont_res.subject_id(+)

          AND  cont_res.subject_table(+) = 'HZ_PARTIES'

          AND  role_acct.account_number = I.ACCOUNT_NUMBER

          AND  role_acct.cust_account_id = hcasa.cust_account_id

          AND  hcasa.cust_acct_site_id = acct_role.cust_acct_site_id

          AND  hcasa.cust_acct_site_id = hcu.cust_acct_site_id

          AND  resp.cust_account_role_id = acct_role.cust_account_role_id

          AND  resp.responsibility_type ='BILL TRUST EMAIL'

          AND  hps.party_site_number  = I.BILL_TO_SITE;

select object_version_number 

into l_obj_num

from hz_cust_account_roles

where CUST_ACCOUNT_ROLE_ID = l_role_id

and rownum <2;

  l_contact_point_rec.CUST_ACCOUNT_ROLE_ID    := l_role_id;  -- Role id from hz_cust_account_roles

  --l_contact_point_rec.party_id      := 18392156;

  l_contact_point_rec.role_type      := 'CONTACT';

  l_contact_point_rec.CUST_ACCOUNT_ID      :=l_cust_acct_id;      --   cust_account_id from hz_cust_accounts   

  l_contact_point_rec.ORIG_SYSTEM_REFERENCE := l_role_id;    -- Role id from hz_cust_account_roles

  l_contact_point_rec.status              := 'A';

 

  HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (

    p_init_msg_list                 =>         FND_API.G_FALSE,

    p_cust_account_role_rec         =>         l_contact_point_rec,

    p_object_version_number         =>         l_obj_num,

    x_return_status                 =>         x_return_status,

    x_msg_count                     =>         x_msg_count,

    x_msg_data                      =>         x_msg_data

);

  dbms_output.put_line('Return Status :' || x_return_status);

  IF x_return_status <> 'S'

  THEN

     FOR k in 1 .. x_msg_count loop

       x_msg_data := fnd_msg_pub.get

                     ( p_msg_index => k

                     , p_encoded   => 'F'

                     ) ;

       dbms_output.put_line('Error:' || x_msg_data);

      

     END LOOP;

      ELSE

       UPDATE xx_bill_to_roll_tbl SET UPD_STATUS  = 'A' 

        WHERE ACCOUNT_NUMBER = I.ACCOUNT_NUMBER 

        AND BILL_TO_SITE = I.BILL_TO_SITE

        AND STATUS = 'I';

  END IF;

END LOOP;

END;

Customer Name Update API

 SET SERVEROUTPUT ON;

SET DEFINE OFF;

DECLARE

  l_organization_rec    HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;

  l_party_rec           HZ_PARTY_V2PUB.PARTY_REC_TYPE;

  --l_party_obj_version NUMBER;

  x_profile_id          NUMBER;

  x_return_status VARCHAR2(1);

  x_msg_count  NUMBER;

  x_msg_data  VARCHAR2(4000);

CURSOR C1 IS SELECT ICNU.CUST_NUMBER, ICNU.CURR_NAME, ICNU.CORRECT_NAME, ICNU.STATUS, HCA.PARTY_ID, 

                hp.OBJECT_VERSION_NUMBER

             FROM XX_CUST_NAME_UPD_TBL ICNU, apps.hz_cust_accounts_all HCA, HZ_PARTIES HP

             WHERE ICNU.STATUS IS NULL

             AND HCA.PARTY_ID = HP.PARTY_ID

             AND ICNU.CUST_NUMBER = HCA.ACCOUNT_NUMBER;

BEGIN

    FOR I IN C1 LOOP

    l_party_rec.party_id                          := I.PARTY_ID;

    l_organization_rec.organization_name          := I.CORRECT_NAME;

    l_organization_rec.party_rec                  := l_party_rec;

    HZ_PARTY_V2PUB.update_organization(p_init_msg_list               => FND_API.G_TRUE,

                                       p_organization_rec            => l_organization_rec,

                                       p_party_object_version_number => I.OBJECT_VERSION_NUMBER,

                                       x_profile_id                  => x_profile_id,

                                       x_return_status               => x_return_status,

                                       x_msg_count                   => x_msg_count,

                                       x_msg_data                    => x_msg_data);

    DBMS_OUTPUT.PUT_LINE('API Status: '||x_return_status);

    

    IF (x_return_status <> 'S')

    THEN

       DBMS_OUTPUT.PUT_LINE('ERROR :'|| x_msg_data );

       UPDATE XX_CUST_NAME_UPD_TBL SET STATUS = 'E'

       WHERE CUST_NUMBER = I.CUST_NUMBER;

       ELSE 

       UPDATE XX_CUST_NAME_UPD_TBL SET STATUS = 'S'

       WHERE CUST_NUMBER = I.CUST_NUMBER;

    END IF;

    DBMS_OUTPUT.PUT_LINE('update_organization is completed');

END LOOP;

    --COMMIT;

EXCEPTION

    WHEN OTHERS

    THEN

       DBMS_OUTPUT.PUT_LINE('Error::::'||SQLERRM);

       ROLLBACK;

END;

Excel Busy Error and solution in Excel4Apps

Open Microsoft Excel  à Add-Ins à Excel Add-ins

Select the Excel4apps related plugins and click OK

Click OK

PO Creation Details Query

SELECT OU_NAME,PO_NUM,PO_PAYMENT_TERMS,SUPPLIER_ITEM_NUM,TYPE,APPROVAL_STATUS,CATEGORY,ORDER_DATE,SUPPLIER,CURRENCY,
AMOUNT,CONTRACT_TYPE,CONV_RATE ,'USD' CONV_CURRENCY, CASE WHEN CONV_RATE IS NULL THEN AMOUNT ELSE
SUM(CONV_RATE*AMOUNT) END CONV_AMOUNT
FROM (
select h.name OU_NAME,
po.segment1 PO_NUM,(SELECT name FROM APPS.AP_TERMS WHERE TERM_ID=PO.TERMS_ID) PO_PAYMENT_TERMS
,pl.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUM,
po.TYPE_LOOKUP_CODE TYPE ,po.AUTHORIZATION_STATUS APPROVAL_STATUS,
mtc.segment1 CATEGORY,po.creation_date ORDER_DATE,aps.vendor_name SUPPLIER
,Po.CURRENCY_CODE CURRENCY,PL.quantity*pl.unit_price AMOUNT,po.attribute10 CONTRACT_TYPE  ,
--apps.gl_currency_api.get_rate('BRL','USD', to_date('02-JAN-2018'),'Corporate')
SUM(GLR.CONVERSION_RATE)  CONV_RATE
from apps.po_headers_all po,
     apps.po_lines_all pl,
     apps.ap_suppliers aps,
     apps.HR_OPERATING_UNITS H,
     apps.mtl_categories mtc,
     apps.GL_DAILY_RATES_V glr
where po.po_header_id=pl.po_header_id
and Po.VENDOR_ID = APS.VENDOR_ID
and po.org_id=h.ORGANIZATION_ID
and mtc.CATEGORY_ID=pl.CATEGORY_ID
and trunc(po.creation_date)>='01-JAN-2018'
and trunc(po.creation_date)<='31-DEC-2019'
--AND GLR.USER_CONVERSION_TYPE = 'Corporate'
and TRUNC(PO.CREATION_DATE) = GLR.conversion_date(+)
AND GLR.FROM_CURRENCY(+) =  PO.CURRENCY_CODE
AND GLR.TO_CURRENCY(+) = 'USD'
and po.org_id in(128,129,147,388,630,367,370,627,628,629,127)
group by h.name,
po.segment1 ,pl.VENDOR_PRODUCT_NUM,
po.TYPE_LOOKUP_CODE,po.AUTHORIZATION_STATUS ,mtc.segment1 ,po.creation_date,aps.vendor_name
,Po.CURRENCY_CODE ,PL.quantity*pl.unit_price,po.attribute10,PO.TERMS_ID
)
GROUP BY OU_NAME,PO_NUM,PO_PAYMENT_TERMS,SUPPLIER_ITEM_NUM,TYPE,APPROVAL_STATUS,CATEGORY,ORDER_DATE,SUPPLIER,CURRENCY,
AMOUNT,CONTRACT_TYPE,CONV_RATE,'USD'
order by order_date asc,po_num;