Chandra R Matta

Trigger any Google Service based on Google Cloud Storage Events

2024-03-17T12:40:00.022-04:00

Recently, I encountered a requirement that involved triggering a Google Cloud Composer DAG based on events occurring in Google Cloud Storage (GCS).

To illustrate this scenario, let's consider an example: Suppose I have a Composer DAG designed to process data files stored in a GCS bucket. These files may arrive at any time throughout the day, and it's crucial to process them promptly upon arrival since they contain time-sensitive information.

Now, let's explore several solutions to address this requirement:

Option 1: Scheduled DAG Runs

Scheduling a DAG to run at regular intervals throughout the day is a straightforward approach. However, it suffers from several drawbacks:

Potential Overkill: Running the DAG every few minutes, regardless of whether new files are available, can lead to unnecessary resource consumption and costs, especially if the frequency far exceeds the actual arrival rate of files.
Processing Delay: If a file arrives just after a DAG run completes, it will have to wait until the next scheduled run, potentially introducing processing delays of up to the interval period.

For scenarios where files arrive sporadically or with varying frequencies, Option 1 may not be the most efficient solution.

Option 2: Cloud Function with GCS Trigger

Creating a Cloud Function triggered by GCS events offers a more responsive and efficient solution:

Real-Time Processing: By triggering the Cloud Function upon file creation or finalization in GCS, we ensure immediate processing of incoming files, eliminating unnecessary delays.
Dynamic DAG Submission: The Cloud Function can dynamically determine the appropriate DAG to submit based on the incoming file's metadata, allowing for flexibility and automation.

However, this approach has a notable drawback:

Potential Overhead: While the Cloud Function provides real-time processing capabilities, it triggers for every file that arrives in the designated bucket, potentially leading to unnecessary function invocations and associated costs.

Option 3: Pub/Sub Notifications for GCS

Utilizing Pub/Sub notifications for GCS object changes introduces a more refined and scalable solution:

Fine-Grained Triggering: By subscribing a Cloud Function to a Pub/Sub topic associated with specific GCS events (e.g., object finalization in a designated folder), we can ensure that the function only triggers when relevant files are created or modified.
Efficient Resource Utilization: With Pub/Sub notifications, we avoid the overhead of continuously polling GCS for changes and running unnecessary function invocations, leading to more efficient resource utilization and cost savings.
Real-Time Processing with Reduced Overhead: This approach combines the benefits of real-time file processing with reduced overhead, making it an optimal choice for scenarios where responsiveness and efficiency are paramount.

i). Create a topic on the GCS bucket, folder( ex: gs://gc-us-gcs-xyz-bkt/stg/) Ref: https://cloud.google.com/storage/docs/gsutil/commands/notification Sample Code: gsutil notification create -t tpc_xyz_file_stg -f json -e OBJECT_FINALIZE

-p stg/ gs://gc-us-gcs-xyg-bkt ii). Deploy the Cloud Function and associate the Cloud Function to the topic created above. Refer Cloud Function creation documentation to create a Cloud Function. Sample Code: gcloud functions deploy xyz_trigger_cf_gcs_events --runtime python312

--region us-east4

--ingress-settings=internal-and-gclb --entry-point xyz_trigger_cf_on_gcs_events

--trigger-topic= tpc_xyz_file_stg --source .

Conclusion:

While each option has its merits, Option 3 stands out as the most appropriate solution for triggering Composer DAGs based on GCS events. By leveraging Pub/Sub notifications, we achieve real-time processing with minimal overhead and optimal resource utilization, ensuring timely and efficient handling of time-sensitive data files.

REST API to trigger Airflow/Composer DAG

2023-03-04T11:16:00.000-05:00

REST APIs are a popular way for applications to communicate with each other, allowing for flexible and scalable interactions. In this blog post, we'll explore how to use Google Cloud Run to trigger an Airflow/Composer DAG through a REST API.

Google Cloud Run is a fully managed compute platform that allows you to run stateless containers that are automatically scaled to meet incoming traffic. Airflow is an open-source platform to programmatically author, schedule and monitor workflows. Composer is a managed version of Airflow by Google Cloud.

Before diving into the implementation, let's first understand what a DAG is. A DAG (Directed Acyclic Graph) is a collection of tasks that are dependent on each other, with a defined order of execution. Airflow uses DAGs to define workflows and their dependencies.

To trigger an Airflow/Composer DAG using a REST API, we can create a simple Python Flask app that sends a request to the Airflow/Composer REST API endpoint. The REST API endpoint can be used to trigger a DAG run by passing a JSON payload that contains the DAG ID, and any other necessary parameters.

Here's an example of how to create a Flask app to trigger a DAG using the Airflow REST API:

from flask import Flask, request

import requests

app = Flask(__name__)

# Airflow/Composer REST API endpoint

AIRFLOW_ENDPOINT = 'https://airflow.googleapis.com/v1beta1/dags/<dag_id>/dagRuns'

# Service account credentials

HEADERS = {

'Authorization': 'Bearer <access_token>',

'Content-Type': 'application/json'

}

@app.route('/trigger-dag', methods=['POST'])

def trigger_dag():

# Get payload data from request body

payload = request.get_json()

# Build request data

data = {

'conf': payload.get('conf', {}),

'replace_microseconds': 'false'

}

# Send request to Airflow REST API

response = requests.post(AIRFLOW_ENDPOINT, json=data, headers=HEADERS)

if response.status_code == 200:

return 'DAG triggered successfully', 200

else:

return 'Failed to trigger DAG', response.status_code

In this example, we have defined a route '/trigger-dag' that accepts a POST request with a JSON payload. The payload contains any necessary parameters for the DAG run. The Flask app sends a request to the Airflow/Composer REST API endpoint using the requests library.

The HEADERS variable contains the service account credentials required to authenticate with the Airflow/Composer REST API. Replace <access_token> with the actual access token for the service account that has the necessary permissions to trigger DAGs.

Replace <dag_id> with the actual ID of the DAG that you want to trigger. You can find the DAG ID in the Airflow/Composer UI or by running the command airflow list_dags in the Cloud Shell.

Once you have created the Flask app, you can deploy it to Google Cloud Run using the following command:

gcloud run deploy <service_name> --image gcr.io/<project_id>/<image_name> --platform managed

Replace <service_name> with the desired name for the Cloud Run service, <project_id> with your Google Cloud project ID, and <image_name> with the name of the container image that contains the Flask app.

Finally, you can test the REST API by sending a POST request to the Cloud Run service URL with the JSON payload. The DAG should trigger successfully, and you can monitor the status of the DAG run in the Airflow/Composer UI.

In conclusion, using Google Cloud Run to trigger an Airflow/Composer DAG through a REST API is a simple and scalable solution

You may check this code repository for working solution

Working code

PL/SQL REST API Call to receive JSON Payload

2020-12-10T17:12:00.005-05:00

declare

req utl_http.req;

res utl_http.resp;

lv_url varchar2(4000) := 'http://<host>:<port>/<something>/';

buffer varchar2(32767);

lj_json_obj json_object_t;

BEGIN

dbms_output.put_line(lv_url);

req := utl_http.begin_request(lv_url,'POST','HTTP/1.1');

utl_http.set_header(req,'Content-type','application/json');

utl_http.set_header(req, 'Accept', 'application/json');

res := utl_http.get_response(req);

-- process the response from the HTTP call

begin

utl_http.read_text(res, buffer,1000000);

lj_json_obj := json_object_t(buffer);

utl_http.end_response(res);

exception

when utl_http.end_of_body then

utl_http.end_response(res);

end ;

end if;

end;

Python- Pandas

2019-10-08T16:29:00.000-04:00

Read individual field values from a data frame

db_df = pd.read_sql("select brms_file_check_notify_pkg.decrypt(a.login_id) password_dr,

a.* from file_check_notification a"                    " where sysdate > next_run and file_check_id = 3"                       , ebsconn)
ebsconn.close()
print('DB is queried for tab1..')
hostname = db_df.loc[0].at['host']

username = db_df.loc[0].at['login_id']
password = db_df.loc[0].at['password_dr']
location = db_df.loc[0].at['location']
subject  = db_df.loc[0].at['email_subject']
protocol = db_df.loc[0].at['protocol_type']

2017-04-24T13:53:00.001-04:00

Numbers to Words Converter

Oracle Analytic Functions - Difference between ORDER BY and PARTITION BY

2017-01-26T17:58:00.000-05:00

ORDER BY - brings up the running total/sum by certain repeating column
PARTITION BY - brings up the GROUP total by certain repeating column

Consider following simple table.

This projects example contains 2 projects - ABC and CAB
Each project contains 2 tasks and its cost respectively.

By using same Analytic functions with two different window, can get "running totals" and "group totals"

select project_id, project_name, task_no, cost
,sum(cost) over(order by project_id,task_no) running_total_by_project_task
,sum(cost) over(order by project_id) running_total_by_project
,sum(cost) over(partition by project_id) total_cost_by_project
,sum(cost) over(partition by task_no) total_cost_by_task
from temp_project_details;

Output below..

Getting Concurrent Request Id within Sql Loader based Concurrent Programs

2015-10-29T17:24:00.002-04:00

None of the following will work in SQL LOADER.
- fnd_global.conc_request_id
- fnd_profile.value('conc_request_id')

First will update request_id as -1 and second will update as blank/NULL

You may consider this.. it works for me.

- Create a function, something like below...
===
CREATE OR REPLACE PACKAGE BODY

IS

-- define global variable..

gn_sqlldr_req_id NUMBER ;

FUNCTION get_request_id RETURN NUMBER
IS
ln_req_id NUMBER ;
BEGIN

IF gn_sqlldr_req_id IS NULL then

select max(request_id)
INTO gn_sqlldr_req_id
from fnd_concurrent_requests where concurrent_program_id in (
select concurrent_program_id from fnd_concurrent_programs where concurrent_program_name =' YOUR_PROG_SHORT_NAME') ;
END IF ;

RETURN gn_sqlldr_req_id ;

END ;

====
Your SQL Loader control file..
==
load data
INFILE '/tmp/XMITINVD.TXT'
INTO TABLE xxinv_TABLE_stg
APPEND
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

(

---

request_id ".get_request_id" -- your function here

)

This worked for me.

How to divide huge data into batches

2015-03-27T10:15:00.004-04:00

Here is my answer on the subject above.

Using an Analytical function – NTILE, I was able to divide big volume of data into multiple batches.

After dividing data into multiple batches, it’s easy to implement multi-threading, without missing any record.

Following query divides Items(for the Org: 489 ) data in to 5 equal batches, and returns MIN and MAX Item ids from each batch.

select min(inventory_item_id) min_item_id , max(inventory_item_id) max_item_id,

count(*) batch_count, batch batch_num

from ( select inventory_item_id, NTILE(5) over (order by inventory_item_id) batch

from mtl_system_items_b

where organization_id = 679 -- 048

) group by batch

Query Output:

MIN_ITEM_ID	MAX_ITEM_ID	BATCH_COUNT	BATCH_NUM
158281	299596	7092	1
299598	400344	7092	2
509671	589229	7091	4
589230	961093	7091	5
400345	509659	7092	3

I used this batching concept to process my data into multi-threading. Sample code below.

Sample Code:

---

for rec in (select min(inventory_item_id) min_item_id , max(inventory_item_id) max_item_id,

count(*) batch_count, batch batch_num

from ( select inventory_item_id, ntile(5) over (order by inventory_item_id) batch

from mtl_system_items_b

where organization_id = <489>) group by batch

) loop

print.log_message ('batch_num:'||rec.batch_num

||':min_item_id:' ||rec.min_item_id

||':max_item_id:'||rec.max_item_id

||':batch_count:'||rec.batch_count);

ln_request_id := 0;

begin

-- submit programs in batchhes..

ln_request_id :=

fnd_request.submit_request (

application => 'XXCUST',

program => 'concp_short_name',

argument1 => xv_item_code,

argument2 => rec.min_item_id,

argument3 => rec.max_item_id,

argument4 => xn_org_id

);

commit;

end loop ;

BI Publisher Bursting - Delivering/sending emails conditionally

2015-01-12T10:59:00.004-05:00

Here is the sample code to send emails conditionally from BI/XML Publisher bursting feature.

Problem Statement:

Generate payslips for all the employees at one shot and email the payslips to respective employee.

1. Alter EMP table and added a column called email to hold emails of each individual employees.

And then update email column to add email Ids where you want your emails to be delivered.

2. Create a Data template Sample code available here

3. Default package Sample code available here

4.Define a concurrent program and assign the concurrent program to a responsibility as per your convenience.

5. Design a simple RTF template and register the same. Sample template available here

6. Register data template, created in #1 above

7. Register control file for bursting. Sample code available here

Package Sample code here.

8. Submit the concurrent program defined in #4. If everything goes fine,
you should be able to see emails delivered at the email ids provided.

Sample Data in XML format.

9. Sample output below..

Extensible Atrributes

2013-10-10T10:42:00.003-04:00

Here is the query to list out all extensible attribute group names, attribute names and its data types.

SELECT *
FROM (SELECT --egoattributeeo.attr_id,
egoattributeeo.application_id,
egoattributeeo.attr_group_type,
egoattributeeo.attr_group_name,
egoattributeeo.attr_name,
egoattributeeo.attr_display_name,
decode(egoattributeeo.data_type_code,'C','CHAR','N','NUM','X','DATE') data_type_code,
egoattributeeo.DEFAULT_VALUE,
egoattributeeo.value_set_name,
egoattributeeo.maximum_size,
egoattributeeo.enabled_flag,
egoattributeeo.required_flag,
egoattributeeo.database_column,
egoattributeeo.read_only_flag
FROM ego_attrs_v egoattributeeo, ego_fnd_dsc_flx_ctx_ext ext
WHERE egoattributeeo.application_id = ext.application_id
AND egoattributeeo.attr_group_type =
ext.descriptive_flexfield_name
AND egoattributeeo.attr_group_name =
ext.descriptive_flex_context_code) qrslt
WHERE (application_id = AND attr_group_type like '%'
)
ORDER BY attr_group_type, attr_group_name

-- All the attribute groups --
SELECT FL_CTX_EXT.ATTR_GROUP_ID ATTR_GROUP_ID ,
FL_CTX.APPLICATION_ID APPLICATION_ID ,
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME ATTR_GROUP_TYPE ,
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME ,
TL.DESCRIPTIVE_FLEX_CONTEXT_NAME ATTR_GROUP_DISP_NAME ,
TL.DESCRIPTION DESCRIPTION ,
FL_CTX.ENABLED_FLAG ENABLED_CODE ,
L1.MEANING ENABLED_MEANING ,
FL_CTX_EXT.MULTI_ROW MULTI_ROW_CODE ,L2.MEANING MULTI_ROW_MEANING ,
FL_CTX_EXT.VIEW_PRIVILEGE_ID VIEW_PRIVILEGE ,
FUNC_VIEW_TL.USER_FUNCTION_NAME VIEW_PRIVILEGE_NAME ,
FL_CTX_EXT.EDIT_PRIVILEGE_ID EDIT_PRIVILEGE ,
FUNC_EDIT_TL.USER_FUNCTION_NAME EDIT_PRIVILEGE_NAME ,
FL_CTX_EXT.AGV_NAME AGV_NAME ,
FL_CTX_EXT.REGION_CODE REGION_CODE ,
FL_CTX_EXT.BUSINESS_EVENT_FLAG BUSINESS_EVENT_FLAG ,
L3.MEANING BUSINESS_EVENT_MEANING ,
'N' IS_EDITABLE ,
FL_TL.TITLE AGT_DISP_NAME ,
FL_CTX_EXT.PRE_BUSINESS_EVENT_FLAG PRE_BUSINESS_EVENT_FLAG ,
L4.MEANING PRE_BUSINESS_EVENT_MEANING ,'N' IS_DELETEABLE
FROM FND_DESCR_FLEX_CONTEXTS FL_CTX ,
EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT ,
FND_DESCR_FLEX_CONTEXTS_TL TL ,
FND_DESCRIPTIVE_FLEXS_TL FL_TL ,
FND_LOOKUP_VALUES L1 ,
FND_LOOKUP_VALUES L2 ,
FND_LOOKUP_VALUES L3 ,
FND_LOOKUP_VALUES L4 ,
FND_FORM_FUNCTIONS_TL FUNC_VIEW_TL ,
FND_FORM_FUNCTIONS_TL FUNC_EDIT_TL
WHERE FL_CTX.APPLICATION_ID = FL_CTX_EXT.APPLICATION_ID AND
FL_CTX.APPLICATION_ID = TL.APPLICATION_ID AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = FL_TL.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE AND
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE AND
TL.LANGUAGE = USERENV('LANG') AND FL_TL.LANGUAGE = USERENV('LANG') AND
L1.LOOKUP_TYPE = 'YES_NO' AND
L1.LOOKUP_CODE = FL_CTX.ENABLED_FLAG AND
L1.LANGUAGE = USERENV('LANG') AND
L1.VIEW_APPLICATION_ID = 0 AND
L2.LOOKUP_TYPE = 'YES_NO' AND
L2.LOOKUP_CODE = FL_CTX_EXT.MULTI_ROW AND
L2.LANGUAGE = USERENV('LANG') AND
L2.VIEW_APPLICATION_ID = 0 AND
L3.LOOKUP_TYPE(+) = 'YES_NO' AND
L3.LOOKUP_CODE (+)= FL_CTX_EXT.BUSINESS_EVENT_FLAG AND
L3.LANGUAGE (+)= USERENV('LANG') AND
L3.VIEW_APPLICATION_ID(+) = 0 AND
L4.LOOKUP_TYPE(+) = 'YES_NO' AND
L4.LOOKUP_CODE (+)= FL_CTX_EXT.PRE_BUSINESS_EVENT_FLAG AND
L4.LANGUAGE (+)= USERENV('LANG') AND
L4.VIEW_APPLICATION_ID(+) = 0 AND
FL_CTX_EXT.VIEW_PRIVILEGE_ID = FUNC_VIEW_TL.FUNCTION_ID(+) AND
FUNC_VIEW_TL.LANGUAGE (+)= userenv('LANG') AND
FL_CTX_EXT.EDIT_PRIVILEGE_ID = FUNC_EDIT_TL.FUNCTION_ID(+) AND
FUNC_EDIT_TL.LANGUAGE (+)= userenv('LANG') AND
FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE NOT IN ('ItemDetailImage', 'ItemDetailDesc')
--and FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'PIM_CLF'

When updating Supplier Address, oracle.apps.ap.supplier.event was not raised

2013-09-24T09:29:00.001-04:00

Problem: Business event "oracle.apps.ap.supplier.event" may not be raised when you updating Supplier Address at site level.

Solution: You need to check the checkbox 'Update to all sites using this address'.

This business event "oracle.apps.ap.supplier.event" will be raised when you do following actions on suppliers/vendors
- Create Vendor
- Update Vendor
- Create Vendor Site
- Update Vendor Site
- Create Vendor Contact
- Update Vendor Contact
- Update Address Assignments

Critical Tasks in Project Management - typically ERP Implementation

2012-03-29T07:08:00.004-04:00

Hi All,

I have put this list together from my past and previous experience and exposure as Project Lead and Manager. Many of these may or may not be applicable depending Project type, Rollout or upgrade, Implemetation. But ideally these points mentioned below are usually missed and or not under consideration until the situation arises. But project management practise says that one should always be prepared for the worst even before it shows up at the door step. I have seen on several occasions when we as a team had to go back and review the strategy to mitigate the risk of missing the timeline

Project plan should include the below activities

1 Check for prerequisite version of 11i needed for R12 upgrade

2 DB upgrade for supporting/as needed to 10g or 11g

3 Server capacity ( RAM, HDD) increase as needed to support 10g or 11g accordingly

4 Necessary pre-requisite patches to be applied for R12 as given for the corresponding version and research on the new patches released for that version and modules related.

5 Data Conversion/Migration planning

6 Instance planning and availability plan

7 Requirement gathering

8 Requirement signoff

9 Module Related Patches Verification

10 Cut Over Activities

11 Seeded Functional Setup steps.

12 Custom components functional design documents

13 Data Conversion/Migration Functional Design

14 Customization Technical design documents

15 Data Conversion/Migration Technical Design

16 Code Build

17 Unit Testing

18 System Integration Testing

19 User acceptance testing

20 Regression Testing

21 Volume Testing

22 Pre Production activities

23 Cut Over Activities

To top it all these should have bookmarks in the Risk Management document. Ideally each step can be flagged off red depending on the stage or the current status of the project affairs.

Finding Guarantors Case Folder for a Parent case folder

2011-09-16T10:44:00.002-04:00

Below is the query to find Guarantors/Child case folder for a Parent/Dealer case folder along credit analyst assigned to it.

select child_cf.case_folder_number child_case_folder, parent_cf.case_folder_number parent_case_folder, res.source_name
from
ar_cmgt_credit_requests re,
ar_cmgt_case_folders child_cf,
ar_cmgt_case_folders parent_cf,
jtf_rs_resource_extns res
where
parent_credit_request_id = parent_cf.credit_request_id
--and parent_cf.case_folder_number = '14225'
and res.source_name = 'Credit Analyst Name Here'
and parent_cf.review_type is not null
and res.resource_id = parent_cf.credit_analyst_id
and child_cf.credit_request_id = re.credit_request_id
and child_cf.review_type is not null
order by 2 desc

Finding Credit Analyst Name in Oracle Credit Management

2011-09-13T10:20:00.000-04:00

-- To find Credit analyst name for the given case folder name. ------

select source_name credit_analyst_ name, source_job_title Job_Title, case_folder_number, usr.user_name User, usr.description
from jtf_rs_resource_extns res ,
ar_cmgt_case_folders cf,
fnd_user usr
where
res.resource_id = cf.credit_analyst_id
and case_folder_number = ''
and res.user_id = usr.user_id

-- ====== to find out credit analyst assigned to Rule Name -----

select rule_name, result_value , usr.user_name credit_analyst_name , res.resource_id
from fun_rule_details fun, jtf_rs_resource_extns res, fnd_user usr
where rule_name = '' and
fun.result_value = res.resource_id
and res.user_id = usr.user_id

How to fix - Credit usage rule has not been setup

2011-04-19T10:49:00.000-04:00

Credit usage rule has not been setup
When creating credit applications, Guarantor or child workflow may throw an error message - Credit usage rule has not been setup.

Reason could be - Credit classification for the Guarantor might not be setup properly.
Following query would help you to find out list of potential guarantors which would cause an issue while creating credit application.

This query list down the information such as Guarantor Name, Dealer Name and the Customer account profile id and object version number.
--
select p.party_type , p.party_number, p.party_name guarantor_name, p.party_id, hrel.object_id, hrel.object_table_name, hrel.object_type,
orgp.party_name dealer_name, orgp.party_number delaer_number,hzp.cust_account_profile_id, hzp.object_version_number,
p.creation_date, p.last_update_date, p.last_updated_by,ppf.full_name user_
from hz_parties p, hz_relationships hrel, hz_parties orgp, hz_customer_profiles hzp, per_people_f ppf, fnd_user usr
where p.party_name in (select
party_name
from hz_parties hzp where party_id in (select party_id from hz_customer_profiles hz,hz_cust_profile_classes cp
where cp.name = 'DEFAULT' and cp.profile_class_id = hz.profile_class_id and hz.cust_account_id = -1 )
and hzp.party_type = 'PERSON' )
and hrel.subject_id = p.party_id
and orgp.party_id = hrel.object_id
and hzp.party_id = p.party_id
and hzp.cust_account_id = -1
and hrel.object_table_name = 'HZ_PARTIES'
and p.party_type = 'PERSON'
and p.last_updated_by = usr.user_id
and ppf.person_id = usr.employee_id

----

Execute following API to set credit classification correctly.

declare
p_customer_profile_rec_type
hz_customer_profile_v2pub.customer_profile_rec_type;
p_cust_account_profile_id number;
p_object_version_number number;
x_return_status varchar2(2000);
x_msg_count number;
x_msg_data varchar2(2000);
begin

fnd_client_info.set_org_context('&Org_Id');
p_customer_profile_rec_type.cust_account_profile_id := '&Cust_Account_Profile_Id';
p_customer_profile_rec_type.profile_class_id := '&New_profile_class_id';
p_object_version_number := '&object_version_number';

hz_customer_profile_v2pub.update_customer_profile( 'T',p_customer_profile_rec_type, p_object_version_number, x_return_status, x_msg_count, x_msg_data);

dbms_output.put_line('x_return_status = '||substr(x_return_status,1,255));
dbms_output.put_line('Object Version Number = '||to_char(p_object_version_number));
dbms_output.put_line('profile_class_id = '||p_customer_profile_rec_type.profile_class_id);
dbms_output.put_line('x_msg_count = '||to_char(x_msg_count));
dbms_output.put_line('x_msg_data = '|| to_char (x_msg_data,1,255));

if x_msg_count >1 then
for i in 1..x_msg_count loop
dbms_output.put_line(i||'.'||substr(fnd_msg_pub.get(p_encoded=> fnd_api.g_false ), 1, 255)); end loop; end if; end;

commit ;

After executing this, you may retest credit application process again and verify the results.
Hope this helps...

OCM - Dealer Names and Its Guarantor Names

2011-03-21T15:49:00.001-04:00

select p.party_type , p.party_number, p.party_name guarantor_name, p.party_id, hrel.object_id, hrel.object_table_name, hrel.object_type,
orgp.party_name dealer_name, orgp.party_number delaer_number
from hz_parties p, hz_relationships hrel, hz_parties orgp
where p.party_name in (select
party_name
from hz_parties hzp where party_id in (select party_id from HZ_CUSTOMER_PROFILES hz,hz_cust_profile_classes cp
where cp.name = 'DEFAULT' and cp.profile_class_id = hz.profile_class_id and hz.cust_account_id = -1 )
and hzp.party_type = 'PERSON' )
and hrel.subject_id = p.party_id
and orgp.party_id = hrel.object_id

OCM - How to extract Dealer names and its related Owner names

2011-03-21T15:48:00.000-04:00

Select r.party_id party_id, p.party_number dealer_num, p.party_name dealer_name, sub_party.party_name owner_name
from hz_relationships r , hz_parties p, hz_parties sub_party,
where r.subject_id = sub_party.party_id
and r.object_id = p.party_id
and ((r.end_date is null) or (r.end_date > sysdate)) and r.status = 'A'

How to list down all the attachments attached to a vendor or party

2011-03-21T14:45:00.000-04:00

Here is a way to find out all the attachments attached to a specific vendor or specific customer/party etc.

#1. To list down all the attachments attached to each supplier/vendor, following query can be used.

select segment1 vendor_num, vendor_name,
fdoc.title file_title, fdoc.file_name
from ap_suppliers sup, fnd_attached_documents doc, fnd_documents_vl fdoc
where
doc.pk1_value = sup.vendor_id and doc.entity_name = 'PO_VENDORS'
and doc.document_id = fdoc.document_id
order by vendor_name

See the screenshot for sample attachment for one vendor.

#2. In a similar way, to find out attachments for parties..

select hzp.party_name Owner_name, hzp.party_number Owner_number, hzp.party_type, docs.creation_date doc_created_date , fdoc.title file_title, fdoc.file_name
from FND_ATTACHED_DOCUMENTS docs, hz_parties hzp, fnd_documents_vl fdoc
where entity_name like 'HZ_PARTIES' and pk1_value = hzp.party_id
and docs.document_id = fdoc.document_id
order by 1

Thanks,
Chandra.

Enabling and disabling parameters dynamically. -2

2010-04-24T10:38:00.000-04:00

Here is yet another post on same old topic - "Enabling and disabling parameters dynamically"
Well there is a slight change in requirement.
In my first post on this topic, user have a choice of selecting Yes or No, as a first parameter and based on this first parameter selection, we designed second parameter to behave accordingly.

But, what if there is no selection in first parameter.?
Or in other words, value for first parameter is NULL ?

Brief explanation about the requirement.
My concurrent program has got three parameters as below.
1. Receipt Batch Name
2. GL Date From
3. GL Date To.

Initially, when "Receipt Batch Name" is NULL then GL Date parameters should be enable and ready to take values.

If a value is entered for "Receipt Batch Name", then GL Date parameters should be disabled.

Simple check to find first parameter as NULL or NOT NULL will not work. For example, consider the code below.
decode(:$FLEX$. XXAR_RECEIPT_BATCH_NAME,NULL,'IT IS NULL','IT IS NOT NULL')
-- this command is not enough.
You need to check for NULL values of any value set like below.
decode(nvl(:$FLEX$.XXAR_RECEIPT_BATCH_NAME:NULL,'X'),'X','Y',null)

For complete solution, consider following screenshots.

Complete SQL Statement entered as default value is -

select decode(nvl(:$FLEX$.XXAR_RECEIPT_BATCH_NAME:NULL,'X'),'X','Y',null) from dual

Enter following sql statment in place of Table Name

(SELECT trunc(SYSDATE) - 1000 + LEVEL date_range FROM DUAL CONNECT BY LEVEL <= 1000) temp_tab

BI Publisher: How to format a specific column conditionally.

2010-01-30T09:33:00.000-05:00

When I was going through a tech forum, this question got my attention.

"How to format(bold, color,underline etc) specific column conditionally.

Say you have XML data, based on certain flag value, data should be displayed in a special format.

See the actual question here.

How to Underline a column with a specific width

Here is the answer..

I created this test scenario at my end and it works for me.

Consider following data as my XML data file..

=====XML data begins. =====

abc

12,000.00

def

13,000.00

ghi

14,000.00

=====XML data ends. =====

And the way I worked is as follows..

Following statement is the key in this solution

1. Create two IF conditions in your template.

- one Y

- another one for N

2. Inside your IF under_line= Y region, use this font underline statement

3. Inside your IF under_line= N region, use simple amount display field. i.e

For your other requirements..

a. Underline must be of 13 characters length.. do PADding with spaces in your data source itself. So when it come to XML file, your amount column will comes with full 13 characters.

ex: I did LPAD to my data. LPAD(amount,13,' ' )

b. For amount formats(ex: $12,345.60), try this in XML publisher template, else workout this also as a part of your XML data source(i.e Oracle Reports, or any..).

Complete code can be downloaded here..

In the same way you can do lot of other font formats like

etc..

Happy surfing.

Complex Service PO

2010-01-02T00:36:00.000-05:00

Question: How to customize Complex Service PO in R12.

Answer:
Let's try to discuss about this report in our random walk through.

Below is the query to listdown all the "complex service POs' in the system. Of course you need to set org profile for this query by "begin mo_global.set_policy_context('S',) ; end ; "
==
select * from po_headers ph, po_doc_style_lines_tl psl
where psl.style_id = ph.style_id
and psl.document_subtype(+) = ph.type_lookup_code
and psl.language(+) = userenv('LANG')
and psl.display_name = 'Complex Service Purchase Order'
==

For many reasons Standard template(provided by Oracle) "PO_STANDARD_XSLFO" alone is not sufficient for your requirement.
Example:
- company logo need to be added.
- layout need to be changed
- hide/show additional columns/information
- show registration number of your company. etc

For minor customizations, standard template can be modified and used. But remember standard template is XSL. So it requires some specialized skill to work on XSL.

I had huge amount customizations and all these customizations can not done using standard template. So I had to design and use my own template.
You can visit my template here.

How to design custom template ?

If you decided to proceed with your own template, then the total process(design, define, attach template) is no different with any other BI Publsiher reports.

To design RTF(or any other) template, you need to have XML file.
Getting XML file/data for this customization is not an easy task.
Oracle posted few notes on this report, but none of them discussed how to get XML datafile.

"PO Output for Communication" - is a Java concurrent program.
When you create complex purchase order, PO workflow will trigger "PO Output for Communication" program. This program generates XML file and apply this XML file to template and generate required PDF as output.

In the process described above, system will not save XML file anywhere.

To get XML data file, you need to follow the steps mentioned below.
- Goto PO Responsibility.
- Goto SRS(Conc. program submission) Window
- Enter program "PO Output for Communication"
- Parameters
- PO Number ->
- Test -> Debug
- Template Name -> Standard Purchase Order Stylesheet.

- Submit.
After a while, this program will be completed in error. Don't worry about why it errored out. Just go through the logfile and find XML file in there.
Along with the XML data, there will be lot of other information, which you don't need.

Save the log file in your machine and remove complete text which is outside XML file. That means the text outside of XML tags and . Save this edited file as new file with .XML as extension.
Do following changes to your XML file now.
- Add "xml version="1.0" encoding="UTF-8" ?>" as a first line.
- remove complete text under the tag , as this is not correctly formatted.

Now your XML data file is ready and you can proceed for creating custom template.

Once done with the template design, here are the steps to register and use custom template.
Log in as "XML Publisher Administrator"
- create XML Template for the data definition: Standard Purchase Order Data Source.
- Note: Select Application as “Purchasing’.

- Nav: Purchase Order Super User
- Setup/Purchasing/Document types/
- Locate “Purchase Order Standard” and then Set the Document Type Layout to your new custom template.

Now you are ready test your customizations.
- Create complex service Purchase orde r.
- View PDF. You should be able to see your customizations now.

How to change/modify Data Definition ?

So far so good..
We have seen how to customize RTF template to produce desired output as per your requirements.

How about having additional information, which is not even available in your XML data file.?
Or in other words, how do you change/customize data definition itself, so that your new XML file come up with additional information you want.

Well, there is no direct way to change data definition as concurrent program is of JAVA type.
So you cannot customize this JAVA program(at least, I couldn't ).

In my case, I had to show "Project Code" for each PO distribution, if the procurement is project related.
Another requirement was - Legal Entity Name

Now - my requirements are clear ..show
- Project Code
- Regd Number and Regd. Legal Entity Name on the header portion of the report.
Since I could not alter the data definition( PO Output for Communication), I did following.

Some how I figured out following views are being used by "PO Output for Communication" to produce XML data file
- PO_HEADERS_XML
- PO_LINES_XML
- PO_LINE_LOCATIONS_XML.
- PO_DISTRIBUTION_XML .. etc

Altered "PO_DISTRIBUTION_XML" view to include project information.
eg:
--
CREATE OR REPLACE FORCE VIEW APPS.PO_DISTRIBUTION_XML
(
   AMOUNT_ORDERED,

PA.SEGMENT1 PROJECT_CODE
   FROM PER_ALL_PEOPLE_F PAP,
   PO_DISTRIBUTIONS_ALL PD,
PA_PROJECTS_ALL PA,

WHERE GCD.CODE_COMBINATION_ID(+) = PD.CODE_COMBINATION_ID

AND PAP.PERSON_ID(+) = PD.DELIVER_TO_PERSON_ID

AND PD.PROJECT_ID = PA.PROJECT_ID(+)

AND SYSDATE BETWEEN PAP.EFFECTIVE_START_DATE(+)

AND PAP.EFFECTIVE_END_DATE(+);

---

Now my new XML datafile comes with Project code, which can be displayed in my output/pdf.

PS: To see your changes to the view, it is better to create new PO and generate XML for that PO.

This is because, system caches XML data and reuses that.. thats the reason it is better to create brand new POs and test it.

Following few screenshots are to view your PDF for PO from SRS window.
This way, you don't need to regenerate your PO or revise your PO.
Without increasing revision numbers, you can see all your PO updates in your PDF.

PS: I am updating this post, as and when I get some free time.
So information could be assorted and zig-zag.

Return statement in PL/SQL Procedure.

2010-01-01T23:23:00.000-05:00

Question: Can we use RETURN Statement in PL/SQL Procedure.?
Ans: Yes, we can !! ;)

Honestly, as far as I remember, I've never used return statements in my PL/SQL Procedures.
How about you ??

Of coarse, in PL/SQL functions RETURN statement is a mandatory thing. But what about procedures ??

Lets see how and where we can use of return statement in a procedure.
- You have procedure, that contains few lines of code.
- After validating certain condition, you don't need to proceed further. You want control to be returned to calling procedure/main program.

** Couple of approaches here **

One way of doing it is, handling USER defined EXCEPTIONs.
- you can check how to use USER defined Exceptions. I am not covering it here. ;)
I used this approach many times until now. Big advantage of this way is, you can trap the ERROR code in your calling program.

Another way is having RETURN statement in your sub-routine/child program.
For example:
== sub-routine ==
procedure CHILD_PROC
-- declarations
begin
-- pl/sql statements
if CONDITION = TRUE then
RETURN ;
else
-- do something
end if ;
end CHILD_PROC ;

=== calling program ==
procedure MAIN_PROC
-- declarations
begin
-- pl/sql statements
-- call to child proc
CHILD_PROC();
-- few more statements
end MAIN_PROC ;

Here is some sample code.

-- child procedure.

create or replace procedure child_proc(p_boolean BOOLEAN) as
begin
dbms_output.put_line('in child_proc before return call');
if p_boolean then
return ;
end if ;
dbms_output.put_line('in child_proc after return');
end;
/

-- main procedure

create or replace procedure main_proc(p_boolean boolean) as
begin
dbms_output.put_line('before child_proc call');
child_proc(p_boolean) ;
dbms_output.put_line('after child_proc call');
end;
/

-- execution and result check --

[1]

begin main_proc(TRUE); end ;

--output --

before child_proc call

in child_proc before return call

after child_proc call

Statement processed.

0.00 seconds

[2]

begin main_proc(FALSE); end ;

--output --

before child_proc call

in child_proc before return call

in child_proc after return

after child_proc call

Statement processed.

0.00 seconds

HAPPY NEW YEAR - 2010

2010-01-01T09:23:00.000-05:00

Hi Friends.. HAPPY NEW YEAR - 2010 .!!

Enabling/disabling DATE parameters of a concurrent program

2009-11-10T22:28:00.000-05:00

Question: How to enable/disable date parameters of a concurrent program based on certain condition.?
Answer: Handling number and text parameters had been discussed in the following blogs.
http://apps2fusion.com/at/ar/241-enablingdisabling-concurrent-program-parameters
and one more at http://sureshvaishya.blogspot.com/.

My requirement was different. I wanted to enable/disable DATE parameters based on user input.

Refer to the screenshots to understand what the intended functionality is ..

As shown in the screen, If the value for "Include Manual Invoices" parameter is "Yes", then enable and accept values for parameters "Invoice Txn Date From" and "Invoice Txn Date To".

If the value for "Include Manual Invoices" parameter is "No", then disable parameters "Invoice Txn Date From" and "Invoice Txn Date To".

Let's get to details, how we can implement the functionality discussed above.

I am not going to discuss other parameters, which are not related to Enabling/disabling - functionality.

Note: As you might have noticed, I intentionally removed/masked custom names while taking screenshots.

Ex: Conc. Program Name, Application Name etc.

Steps:

1. Define controlling parameter, which can be based on any "Yes_or_No" valueset.

In my example - I used valueset called "CST_SRS_SYS_YES_NO".

2. Define parameter - P_HIDDEN_PARAM. As it's name suggests, this is a hidden parameter, which will not be visible when we submit the conc. program.

This parameter is directly dependant on the previous parameter - "Include Manual Invoices"

Set it's Default Type as "SQL Statement " and

Default value as - "select decode(:$FLEX$.CST_SRS_SYS_YES_NO,'Y','Y',null) from dual".

3. Define custom valueset, which will used for the date parameters - "Invoice Txn Date From" and "Invoice Txn Date To".

- Enter valueset name , Select Format Type as Standard Date. Refer to the screenshot below for other details.

a) Click on Edit Information and enter forllowing query in Table Name field.

(SELECT (TO_DATE (SYSDATE - 200 + LEVEL, 'DD-MON-RRRR')) date_range FROM DUAL CONNECT BY LEVEL <= 1000)temp_tab

I am using "CONNECT BY LEVEL" concept to produce dates with the range of 1000 days.
You can modify 1000 to any other figure as per your requirement.
b) Set Value Name as date_range, which is nothing but the alias name in the query above.
c) Eter following WHERE clause in Where/Order By field.

WHERE :$FLEX$.AMS_SRS_CHAR1='Y'

4. Attach newly created Value Set for the date parameters - "Invoice Txn Date From" and Invoice "Txn Date To" as shown below.

Save your changes and test it. If you have done all the setups correctly, you will see "Enabling/disabling" functionality something like this..

Write to me if you have any questions.

Discoverer 4i to BI Discoverer 10g Migration

2009-11-10T04:25:00.000-05:00

Oracle Corp. recently(one year back) decommissioned it's discoverer 4i and it's previous versions.
This triggered many companies to upgrade from Old versions of Discoverer to Discoverer 10g.
Some time back, I got an opportunity to lead such kind of project with one of my clients.
The objective of this paper is to upgrade the discoverer to the latest supported version.

This paper covers the following topics
Installation and configuration of Oracle Application Server 10g on apps environment
Application of required patches.
Import of existing reports into Discoverer 10g.

Important Notes:
1. Discoverer 10 can be implemented in two models.
a. Standalone Installation
b. Oracle 10g AS with Infrastructure

My client selected "Standalone" method. So my and papers talks about - Standalone, but not Oracle 10g AS with Infrastructure

2. Some companies use Noetix views and many companies may not have implemented Noetix.
a. If your client is not using Noetix views, you can ignore the step - "Noetix Upgrade" in this paper.
b. In my project, it involved Noetix upgradation also.

Steps In Brief
Noetix Upgrade [Optional]
Application Patches
Oracle Bi 10g Installation And Configuration
Configuration With E-Business 11i
Port Information
Patch Application On Oracle Bi 10g
EUL Migration
Pre-Installation Tasks
DB Schema Creation And Import
EUL Upgradation
Post-Installation Tasks
Audit Trail Reports
Rollback Plan

Follow complete documentation at the link below.

http://www.apps2fusion.com/at/40-cm/208-discoverer-4i-to-discoverer-10g-migration-steps

Write to me if you have any questions.