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;

Customer Contact Details Update -- API

SET SERVEROUTPUT ON;
DECLARE
Cursor c1 is
select location_id, POSTAL_CODE from xx_zipcode_tbl
where POSTAL_CODE is not null;
p_location_rec          HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_object_version_number NUMBER;
x_return_status         VARCHAR2(2000);
x_msg_count             NUMBER;
x_msg_data              VARCHAR2(2000);
l_obj_ver               CHAR(2);
BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id      => 111111
                            ,resp_id      => 11111
                            ,resp_appl_id => 222);
mo_global.set_policy_context('S',204);
fnd_global.set_nls_context('AMERICAN');
for i in c1 loop
l_obj_ver := NULL;
SELECT hl.object_version_number INTO l_obj_ver
FROM HZ_LOCATIONS hl
WHERE hl.location_id = i.location_id;

-- Initializing the Mandatory API parameters
p_location_rec.location_id := i.location_id;
p_location_rec.postal_code    := i.POSTAL_CODE;

hz_location_v2pub.update_location
            (
             p_init_msg_list           => FND_API.G_TRUE,
             p_location_rec            => p_location_rec,
             p_object_version_number   => l_obj_ver,
             x_return_status           => x_return_status,
             x_msg_count               => x_msg_count,
             x_msg_data                => x_msg_data
                  );
             
IF x_return_status = fnd_api.g_ret_sts_success THEN
UPDATE xx_zipcode_tbl SET STATUS = 'Y' WHERE LOCATION_ID = I.LOCATION_ID;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('New POSTAL CODE : '||p_location_rec.postal_code||'-'||p_location_rec.location_id);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Location failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
/

Print Flag Details of Customer statement Query

select cust_acct.account_number customer_number,
party_site.party_site_number site_number,
hp.PARTY_NAME, RCTA.TRX_DATE,rcta.trx_number,
RCT.name, PRINTING_ORIGINAL_DATE,PRINTING_LAST_PRINTED
FROM APPS.ra_customer_trx_all rcta,
APPS.hz_cust_accounts cust_acct,
APPS.hz_party_sites party_site,
APPS.hz_cust_acct_sites_all acct_site,
APPS.hz_cust_site_uses_all site_uses,
APPS.hz_parties hp,
APPS.ra_cust_trx_types_all rct
where  rcta.ship_to_customer_id = cust_acct.cust_account_id
AND rcta.ship_to_customer_id = acct_site.cust_account_id
AND rcta.ship_to_site_use_id = site_uses.site_use_id
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'SHIP_TO'
AND party_site.party_site_id = acct_site.party_site_id
AND hp.party_id = cust_acct.party_id
AND hp.party_id = party_site.party_id
AND rct.CUST_TRX_TYPE_ID = rcta.CUST_TRX_TYPE_ID
--AND rcta.CUST_TRX_TYPE_ID = 1225
and rcta.trx_number in ('170169','170170');

AP_Distribution_With_GL_Batch_Names Query

select *
from (
SELECT org.name,
  v.vendor_name,
  v.segment1 vendor_num,
  vs.vendor_site_code,
  api.invoice_num,
  api.doc_sequence_value voucher_num,
  api.invoice_type_lookup_code,
  api.invoice_date ,
  api.tax_invoice_recording_date fulfillment_date,
  api.invoice_currency_code,
  apd.accounting_date,
  'DIST' acc_source,
  apd.distribution_line_number line_number,
  apd.line_type_lookup_code,
  zxc.tax_rate_code,
  u1.user_name created_by,
  u2.user_name last_updated_by,
  glv.concatenated_segments gl_flexfield,
  glv.segment1 company,
  glv.segment2 location,
  glv.segment3 product,
  glv.segment4 FUNCTION,
  glv.segment5 gl_account,
  glv.segment6 intercompany,
  glv.segment7 subaccount,
  apd.description,
  apd.posted_flag,
  apd.amount,
  apd.base_amount,
  gl_link1.gl_batch_name primary_gl_batch,
  gl_link2.gl_batch_Name secondary_gl_batch
FROM
  apps.ap_invoices_all api ,
  apps.ap_suppliers v ,
  apps.ap_supplier_sites_all  vs ,
  apps.ap_invoice_distributions_all apd ,
  apps.hr_organization_units org ,
  apps.gl_code_combinations_kfv glv ,
  apps.fnd_user u1 ,
  apps.fnd_user u2 ,
  apps.zx_rates_vl zxc,
  (
  select
gle.name ledger_name,
gle.ledger_category_code,
glb.name gl_batch_name,
xdl.source_distribution_id_num_1
from apps.xla_distribution_links xdl,
 apps.ap_invoice_distributions_all apd,
 apps.xla_ae_lines xal,
 apps.xla_ae_headers xah,
 apps.gl_import_references gli,
 apps.gl_je_lines gll,
 apps.gl_je_headers glh,
 apps.gl_je_batches glb,
 apps.gl_ledgers gle
where 1=1
and xal.ae_header_id = xah.ae_header_id
and xah.ledger_id = gll.ledger_id
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.accounting_line_code <> 'AP_LIAB_INV'
and xdl.application_id = 200
and xdl.source_distribution_id_num_1 = apd.invoice_distribution_id
-- and apd.invoice_id = 4726023
and xdl.ae_header_id = xal.ae_header_id
and xdl.ae_line_num = xal.ae_line_num
and xal.gl_sl_link_id = gli.gl_sl_link_id
and gli.je_header_id = gll.je_header_id
and gli.je_line_num = gll.je_line_num
and gll.je_header_id = glh.je_header_id
and glh.je_batch_id = glb.je_batch_id
and gle.ledger_id = gll.ledger_id
) gl_link1,
 (
  select
gle.name ledger_name,
gle.ledger_category_code,
glb.name gl_batch_name,
xdl.source_distribution_id_num_1
from apps.xla_distribution_links xdl,
 apps.ap_invoice_distributions_all  apd,
 apps.xla_ae_lines xal,
 apps.xla_ae_headers xah,
 apps.gl_import_references gli,
 apps.gl_je_lines gll,
 apps.gl_je_headers glh,
 apps.gl_je_batches glb,
 apps.gl_ledgers gle
where 1=1
and xal.ae_header_id = xah.ae_header_id
and xah.ledger_id = gll.ledger_id
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.accounting_line_code <> 'AP_LIAB_INV'
and xdl.application_id = 200
and xdl.source_distribution_id_num_1 = apd.invoice_distribution_id
-- and apd.invoice_id = 4726023
and xdl.ae_header_id = xal.ae_header_id
and xdl.ae_line_num = xal.ae_line_num
and xal.gl_sl_link_id = gli.gl_sl_link_id
and gli.je_header_id = gll.je_header_id
and gli.je_line_num = gll.je_line_num
and gll.je_header_id = glh.je_header_id
and glh.je_batch_id = glb.je_batch_id
and gle.ledger_id = gll.ledger_id
) gl_link2
WHERE 1                =1
and gl_link1.source_distribution_id_num_1(+)  = apd.invoice_distribution_id
and gl_link1.ledger_category_code = 'PRIMARY'
and gl_link2.source_distribution_id_num_1(+)  = apd.invoice_distribution_id
and gl_link2.ledger_category_code = 'SECONDARY'
AND api.invoice_id     = apd.invoice_id
AND api.org_id         = org.organization_id
AND api.vendor_site_id = vs.vendor_site_id(+)
AND vs.vendor_id       = v.vendor_id
AND apd.dist_code_combination_id = glv.code_combination_id(+)
  --and apd.amount <> 0
AND apd.created_by      = u1.user_id(+)
AND apd.last_updated_by = u2.user_id(+)
AND apd.tax_code_id     = zxc.tax_rate_id(+)
UNION
SELECT org.name,
  v.vendor_name,
  v.segment1 vendor_num,
  vs.vendor_site_code,
  api.invoice_num,
  api.doc_sequence_value voucher_num,
  api.invoice_type_lookup_code,
  api.invoice_date ,
  api.tax_invoice_recording_date fulfillment_date,
  api.invoice_currency_code,
  api.gl_date accounting_date,
  'LINE',
  apl.line_number,
  apl.line_type_lookup_code,
  zxl.tax_rate_code,
  u1.user_name created_by,
  u2.user_name last_updated_by,
  glv.concatenated_segments gl_flexfield,
  glv.segment1 company,
  glv.segment2 location,
  glv.segment3 product,
  glv.segment4 FUNCTION,
  glv.segment5 gl_account,
  glv.segment6 intercompany,
  glv.segment7 subaccount,
  apI.description,
  'N' posted_flag,
  apl.amount + nvl(zxl.tax_amt, 1),
  decode(api.invoice_currency_code,
              gll.currency_code, apl.amount+ nvl(zxl.tax_amt, 1),
              (apl.amount * api.exchange_rate) + nvl(zxl.tax_amt_funcl_curr,0)
              ) base_amount ,
 NULL, NULL
FROM
  apps.ap_invoices_all api ,
  apps.ap_invoice_lines_all apl,
  apps.ap_suppliers v ,
  apps.ap_supplier_sites_all vs ,
  apps.hr_organization_units org ,
  apps.gl_code_combinations_kfv glv ,
  apps.fnd_user u1 ,
  apps.fnd_user u2 , apps.zx_lines_v zxl, apps.financials_system_params_all p, apps.gl_ledgers gll
WHERE 1                =1
AND api.invoice_id     = apl.invoice_id
and apl.line_type_lookup_code = 'ITEM'
AND not exists (select 'x' from apps.ap_invoice_distributions_all apd where apd.invoice_id = api.invoice_id)
AND api.org_id         = org.organization_id
AND api.vendor_site_id = vs.vendor_site_id(+)
AND vs.vendor_id       = v.vendor_id
AND apl.default_dist_ccid = glv.code_combination_id(+)
  --and apd.amount <> 0
and 2=2
and apl.invoice_id=zxl.trx_id(+)
and zxl.application_id(+) = 200
and zxl.entity_code(+) = 'AP_INVOICES'
and zxl.event_class_code(+) in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
and apl.line_number=zxl.trx_line_number(+)
and 3=3
AND apl.created_by      = u1.user_id(+)
AND apl.last_updated_by = u2.user_id(+)
and 4=4
and p.set_of_books_id = gll.ledger_id
and p.org_id = apl.org_id
) invdist
where 1=1
AND invdist.accounting_date BETWEEN to_date(:P_GL_Date_From, 'DD-MON-RR hh24:mi:ss')
                                              AND to_date(:P_GL_Date_To, 'DD-MON-RR hh24:mi:ss')

ORDER BY 1,  5,  11;

Oracle Integration Cloud Development Lifecycle

OIC Integration Cloud Development Lifecycle

•Create Connections

•Create Integrations

•Map data

•Activate Integration

•Monitor Integrations

OIC Integration Development Lifecycle

Integrations: Integrations are processes that utilize connections (among other things) to implement a business process.

Connections: Connections are the various connectors that can be utilized to communicate with external applications. Communication can be done with conventional systems like File, FTP, etc., and also with new social media and other applications, like LinkedIn, Facebook, Twitter, Evernote, etc.

Lookups: A bit like DVMs in SOA Suite, can cross-reference values of the same attribute/element when mapping from one system to another.

Packages: A bit like partitions in EM console, can be used to group together similar integrations.

Agents: Agents are used to connecting to on-premise applications. For example, if you are using on-premise systems like ERP/SAP and also using Oracle OIC in the same implementation, you would have to use an agent to connect to the OIC application.

Adapters: Adapters lists all the adapters that are available for external communication. Currently, about 50 adapters are offered.

Creating Connections

•A connection is defined for any endpoint (source or target) that is implemented within an integration. These connections are based off of defined adapters. Adapters allow for easy connectivity to applications, insulating the developer from complexities that can occur when interacting with an application

•Set of pre defined Adapters

•Steps to create Connection

•Click Create.

•In the Email field, enter an email address to receive email notifications when problems or changes occur in this connection.  Note that a security policy of Basic Authentication is automatically selected. No additional configuration is required.

•In the upper right corner, click Test. The message Connection Hello World was tested successfully is displayed.

•Click Save, then click Close.

Creating Integrations 

•Developing Integration between SaaS application is done through ICS via web based tool

•Message Transformation

•Loookup

•Libraries

•Packages

•Assign

Steps to create 

Integrations are the main ingredient of Oracle Integration Cloud. When create the integration, we build on the connections we already created by defining how to process the data for the connections.

1.In the navigation pane on the left, click Integrations.

2.Click the Got it! button to dismiss any text overlays that are displayed.

3.In the upper right corner, click Create.

4.Select Orchestration as the pattern to use. The Create New Integration dialog is displayed.

5.Enter MyFirstIntegration, then click Create.