API to update Collector Name on AR Customer

Dear Readers

Recently came across a requirement wherein the user wanted to mass-update the collector name on a whole lot of customers to a new one. Apparently the old collector had left the organization and so they wanted this update

To do this, we can use the below API. All we need to do is get the cust_account_id, cust_account_profile_id and the new collector_id from ar_collectors table and we are ready :)

NOTE: This is updating the value at customer level. If you wish to update at the site level, simply also pass the site_use_id into the same record type

DECLARE

   v_customer_profile_rec_type   hz_customer_profile_v2pub.customer_profile_rec_type;

   v_latest_ver_num              NUMBER;

   v_return_status               VARCHAR2 (10);

   v_msg_count                   NUMBER;

   v_msg_data                    VARCHAR2 (2000);

BEGIN

   v_customer_profile_rec_type.cust_account_profile_id := :cust_account_profile_id;

   v_customer_profile_rec_type.cust_account_id := :cust_account_id;

   v_customer_profile_rec_type.collector_id := :new_collector_id;

   SELECT   object_version_number

   INTO     v_latest_ver_num

   from     hz_customer_profiles

   WHERE    cust_account_profile_id = v_customer_profile_rec_type.cust_account_profile_id;

   hz_customer_profile_v2pub.update_customer_profile

         (p_customer_profile_rec          => v_customer_profile_rec_type,

            p_object_version_number       => v_latest_ver_num,

            x_return_status               => v_return_status,

            x_msg_count                   => v_msg_count,

            x_msg_data                    => v_msg_data

         );

END;

Hope this helps

Cheers
A

BOM Routings Data Conversion Sample Script

Dear Readers

Sorry for not having posted anything for a few days, was buried under some work so got time off today to write another post

In this post we will see a sample script for BOM Routing conversion.

The script is as below and is pretty simple and self-explanatory. I hope this helps you

INSERT INTO bom_op_routings_interface

(

   assembly_item_id,

   process_revision,

   process_flag,

   transaction_type,

   routing_type,

   organization_id

)

values

(

   83435,      --inventory item id of the assembly item for which routing is to be made

   null,

   1,          --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).

   'CREATE',

   1,          --1) manufacturing, 2) engineering

   201

);

INSERT INTO bom_op_sequences_interface

(

   assembly_item_id,

   operation_seq_num,

   department_id,

   department_code,

   process_flag,

   transaction_type,

   organization_id,

   effectivity_date

)

values

(

   83435,      --inventory item id of the assembly item for which routing is to be made

   1,          --first sequence in routing

   1,          --Department ID from BOM_DEPARTMENTS_V

   'DEP1',     --Department code from BOM_DEPARTMENTS_V

   1,

   'CREATE',

   201,

   Trunc(SYSDATE)

);

INSERT INTO bom_op_resources_interface

(

   resource_seq_num,

   resource_id,

   resource_code,

   usage_rate_or_amount,

   assigned_units,

   assembly_item_id,

   operation_seq_num,

   process_flag,

   transaction_type,

   effectivity_date,

   organization_id ,

   schedule_flag,

   schedule_seq_num

)

values

(

   1,             --first resource needed in above OP sequence

   2001,          --Resource ID from BOM_RESOURCES_V

   'ELECTRICAL',  --Resource Code from BOM_RESOURCES_V

   1,

   1,

   83435,         --inventory item id of the assembly item for which routing is to be made

   1,             --Operation sequence (inserted above) for which this resource is needed

   1,

   'CREATE',

   Trunc(SYSDATE),

   201,

   2,

   NULL

);

insert into bom_op_sequences_interface

(

   assembly_item_id,

   operation_seq_num,

   department_id,

   department_code,

   process_flag,

   transaction_type,

   organization_id,

   effectivity_date

)

values

(

   83435,            --inventory item id of the assembly item for which routing is to be made 

   2,                --second sequence in routing                                              

   2,                --Department ID from BOM_DEPARTMENTS_V                                   

   'DEP2',           --Department code from BOM_DEPARTMENTS_V                                  

   1,

   'CREATE',

   201,

   Trunc(SYSDATE)

);

INSERT INTO bom_op_resources_interface

(

   resource_seq_num,

   resource_id,

   resource_code,

   usage_rate_or_amount,

   assigned_units,

   assembly_item_id,

   operation_seq_num,

   process_flag,

   transaction_type,

   effectivity_date,

   organization_id ,

   schedule_flag,

   schedule_seq_num

)

values

(

   1,                   --first resource needed in above OP sequence                           

   2006,                --Resource ID from BOM_RESOURCES_V                                     

   'SKILLED',           --Resource Code from BOM_RESOURCES_V                                   

   3,                                                                                     

   1,                                                                                 

   83435,               --inventory item id of the assembly item for which routing is to be made

   2,                   --Operation sequence (inserted above) for which this resource is needed

   1,

   'CREATE',

   Trunc(SYSDATE),

   201,

   2,

   NULL

);

Cheers
A

Easy method to parse data in CSV format into individual fields

Dear Reader

Today we will take a look at how we can very easily parse a line of data in CSV format into its individual columns/fields

We usually use a control file and SQLLDR to load some data from a CSV file into a staging table and then process it. But this involves needing to launch a HOST or SQL*Loader program to perform this task from within your PLSQL program

What if we wanted to keep everything together inside our PLSQL program and not create other programs just to do the load? Simple, follow these steps

1. Put the CSV file in any utl_file_dir location
2. Open it using utl_file and get the data line using utl_file.get_line
3. Now comes the interesting part i.e. parsing the data line. Use the below code and it will return you a PLSQL table containing the individual columns.

DECLARE

   TYPE string_tbl IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;

   x_token_tbl string_tbl;

   p_input_string VARCHAR2(2000) := 'Col1,Col2,Col3,Col4';

   gv_delimiter VARCHAR2(1) := ',';

BEGIN

   SELECT   trim(SUBSTR(t1.str, t1.curr_pos + 1, DECODE(t1.next_pos, 0, LENGTH(t1.str) + 2, t1.next_pos)- t1.curr_pos - 1)) tokens

   BULK COLLECT INTO x_token_tbl

   from

   (

         select   ids str,

                  DECODE(level, 1, 0, INSTR(ids, gv_delimiter, 1, level - 1)) as curr_pos,

                  INSTR(ids, gv_delimiter, 1, level) as next_pos

         from     (SELECT p_input_string ids FROM dual)

         connect by level <= LENGTH(ids) - LENGTH(replace(ids,gv_delimiter,'')) + 1

   ) t1;

   FOR i IN x_token_tbl.FIRST..x_token_tbl.LAST LOOP

      dbms_output.put_line('Value at index: ' || i || ' is : ' || x_token_tbl(i));

   END LOOP;

END;

If you run the above script you would see that "Col1" will be at index-1, "Col2" at index-2 and so on.

Now, you have successfully parsed your CSV data line into its pieces and can go ahead and insert into your staging table ... No host program, No SQLLDR, No control file ... simple right?

Hope this helps

Cheers

A