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.

 

 

Oracle OIC Introduction ....

OIC advantages

Uses a configuration approach with Zero code

Provides an intelligent, drag and drop data mapping tool

Out of box adapters for Cloud Applications, On Premise Applications, FTP, SOAP and REST adapters

Message Exchange Patterns

•Synchronous request/response

•Real time response or error feedback

•Client blocks for response

•Asynchronous request/callback

•Asynchronous request(no callback)

•Event based

  Server receives message based event

  Usually implemented with polling

Agents

•The cloud agents monitor and collect data (for example, metrics, configuration information, and logs) from entities that reside on hosts, or on virtual hosts in a cloud.

•Connect on-Premise Applications

•Create/Download/Install Agents

Integration Style

•Basic Routing

•Publish to OIC

•Subscribe to OIC

•File Transfer

•Scheduled Orchestration

•App Driven Orchestration

Create SOAP adapter Connection in OIC

1. When integrating services with Oracle Integration Cloud service the first thing you need to do is create a connection. Connections define how to connect to a specific instance of an application.

2. In the navigation pane, click Integrations, then click Connections.

3. Click Create.

4. In the Search field enter SOAP and click Search.

5. The Create New Connection dialog is displayed

6. Enter Name, Identifier and optional description of the connection.

Click Create.

7. Your connection is created and you are now ready to configure connection details, such as email contact, connection properties, security policies, connection login credentials, and (for certain connections) agent group.

8. Click on Configure Connectivity, enter the required values.

9. Click Configure Security, enter below and click OK button
UserName:  user name
Password:  password
Confirm Password: password

10. Click on Test button from the upper right corner. If everything is correct then the progress bar will reach 100% and your connection is ready to be used at integration development.