Converting Oracle tables' rows to columns by using LISTAGG

hi All,

Not once I been challenged with this particular issue: how to display multiple rows data in a single column?
There are numerous resources for doing so by using XMLAGG which is fair if you go straight to XML output.
In my case, XML wasn't the goal and I rather prefer to have simple fetch in PL/SQL developer.Good thing on 11g we have WITHIN GROUP and LISTAGG to have it the way I need.
Following post provides detailed example of the feature described at http://www.dba-oracle.com/t_converting_rows_columns.htm

Problem: On the Batch Header there are multiple text paragraphs defined to store varied information:

In our case its a text paragraph called Stability Study Requirements used by Quality Personnel. Every text paragraph (the text stored within) associated with his Batch Header by text_code.
(gme_batch_header.text_code = gme_text_table.text_code):

The problem occurs when same text_code has multiple text lines (long text inserted into the window above) cause single line has only 70 character length, so while trying to fetch the whole Paragraph text we might have several lines retrieved...

Solution: By using within group SQL clause we could pivot multiple Paragraphs' text rows onto a single row and then using the listagg() built-in function to display multiple column values in a single column.


Example: Displaying all Finish Goods' batches that have Stability Study Requirement text paragraph allocated. Whole paragraphs' text displayed as a single column:



select t.batch_no "Batch No",
       t.item_no  "Item Number",
       t.plant_code  "Plant Code",
       text.paragraph_text "Stability Text Paragraph"
from (select    iim.item_no,
                gbh.plant_code,
                gbh.batch_no,
                gbh.text_code
         from   gme_batch_header          gbh,
                gme_material_details      gmd,
                ic_item_mst_b             iim,
                ic_item_mst_b_dfv         im_dfv,
                ic_lots_mst               ilm
         where  1 = 1
           and  gbh.actual_start_date > to_date('01-JAN-2011', 'DD-MON-YYYY')        
           and  gmd.batch_id       = gbh.batch_id
           and  gmd.line_no        = 1
           and  gmd.line_type      = 1
           and  iim.item_id        = gmd.item_id
           and  iim.rowid          = im_dfv.row_id
           and  iim.inactive_ind   = 0
           and  im_dfv.item_type  in 10 --FG
           and  iim.item_id        = ilm.item_id 
           and  ilm.lot_no        <> fnd_profile.value('IC$DEFAULT_LOT')
           and  exists (select 1
                        from gme_text_table_tl  gtt
                        where  gtt.language      = userenv('LANG')
                        and  gtt.lang_code       = userenv('LANG')
                        and  gtt.paragraph_code = 'STAB'
                        and  gtt.text_code = gbh.text_code) 
        group by iim.item_no,
                 gbh.plant_code,
                 gbh.batch_no,
                 gbh.text_code)t
     ,(select gtt.text_code code
              ,listagg (gtt.text, '')
              within group
              (ORDER BY gtt.line_no) paragraph_text
              from gme_text_table_tl         gtt
              where 1 = 1
              and  gtt.language       = userenv('LANG')
              and  gtt.lang_code      = userenv('LANG')
              and  gtt.paragraph_code = 'STAB'
              and  gtt.text is not null
              GROUP BY 
              gtt.text_code)text
where text.code = t.text_code

Existing WIP Jobs' components update

hi All,

Spent some time lately trying to update components of already created Work Orders. Seems to be more "interesting" than I though.
First of all, DO NOT RELY ON THE GUIDE for that particular topic!
The guide (Oracle Manufacturing: APIs and Open Interfaces Manual Release 11i Part No. A95955-03 January 2004) misleads (misguides:)) you  regarding this particular issue.
According to Metalink this piece was fixed in the R12 version of the guide, but 11.5.0 release omitted one crucial piece of information - how to start!
The insertion/creation of new Work Orders explained rather decently - first load header and then jobs details:
1. Insert record into WIP_JOB_SCHEDULE_INTERFACE table
2. Insert record into WIP_JOB_DTLS_INTERFACE table
3.submit WIP Mass Load program
I won't elaborate on that one cause there are several detailed examples for it:
http://oracleappss.blogspot.co.il/2008/07/wip-job-creation.html

Work Orders' update process explained pretty thoroughly, so I gave it a try.
According to guide, in order to update jobs' component, you required  to insert the record for the WIP Job you are looking to update into WIP_JOB_DTLS_INTERFACE and submit WIP Mass Load program.

One thing that seemed to be odd and caught my attention is that in case of job components update, the PARENT_HEADER_ID field: ...Must be NULL if only detail records are loaded or updated. Must
provide WIP_ENTITY_ID and ORGANIZATION_ID.... 


Hmm, took me a while to understand and to overcome an implications of that misleading comment.

Solution: For Update, basically, the same scenario should be used as for the Insert:

1. Insert record into WIP_JOB_SCHEDULE_INTERFACE table
2. Insert record into WIP_JOB_DTLS_INTERFACE table
3. Submit WIP Mass Load program

* do consult the guide regarding the required fields.

Following an actual code sample (I been trying to update SUPPLY_SUBINVENTORY filed):

first get "unique" HEADER_ID and GROUP_ID:

select max(t.header_id)+1,
            max(t.group_id)+1
from wip_job_schedule_interface 


and insert the records as follows (those are mostly the required fields allocated)  :


INSERT INTO wip_job_schedule_interface
(
organization_id
, primary_item_id
, job_name
, wip_entity_id
, group_id
, header_id
, load_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(
87 -- organization_id
,329075 -- primary_item_id
,23555--wip_entity_name
,1877088 -- wip_entity_id
,104 -- max(t.group_id)+1
,64798 -- max(t.header_id)+1
,3 -- load_type
,2 -- process_phase
,1 -- process_status
,4672 -- created_by
,SYSDATE -- creation_date
,4672 -- last_updated_by
,SYSDATE -- last_update_date
)



INSERT INTO WIP_JOB_DTLS_INTERFACE (
 organization_id
, operation_seq_num
, wip_entity_id
, inventory_item_id_old
, wip_supply_type
, date_required
, group_id
, parent_header_id
, load_type
, substitution_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
, SUPPLY_SUBINVENTORY
)
values
(
87 --organization_id
,10 --operation_seq_num
,1877088 --wip_entity_id
,329075 --inventory_item_id_old
,7 --wip_supply_type
,sysdate + 2 --date_required
,104 --max(t.group_id)+1
,64798 --max(t.header_id)+1
,2 --load_type 1. resource 2. component 3. operation 4. multiple resource usage
,3 --substitution_type 1.Delete, 2.Add 3.Change
,2 --process_phase
,1 --process_status
,4672 --created_by
,SYSDATE
,4672 --last_updated_by
, SYSDATE
,'Blah Blah' );

enjoy

Felix

WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY() versus Context Sensitive Descriptive Flexfields

hi,
Lately, I been working on a piece that should populate "Additional Delivery Information" DFF for "local" context (88) and was surprised to see that attributes hadn't been updated.

Methodology:  Custom mobile EBS form updates WSH_NEW_DELIVERIES table attributes - corresponding Oracle API is being called by MWA classes via JDBC connection.

Issue: After API call relevant attributes updated ONLY for Global Data Elements context.

Reference Field for this DFF is ORG_ID, so naturally, while calling this API to allocate Attributes 12 & 13, I would expect them to be saved within "local" Context 88.

x_delivery_info         apps.wsh_deliveries_pub.Delivery_Pub_Rec_Type;

      select tt.name into l_delivery_name
      from wsh_new_deliveries tt 
      where tt.delivery_id = to_number(p_delivery_id);
      
      x_delivery_info.attribute12 := VALUE1;
      x_delivery_info.attribute13 := VALUE2;
      x_delivery_info.delivery_id := to_number(p_delivery_id);
      x_delivery_info.NAME := l_delivery_name;
      x_delivery_info.last_update_date := SYSDATE;
      x_delivery_info.last_updated_by := fnd_global.user_id;
      apps.WSH_DELIVERIES_PUB.CREATE_UPDATE_DELIVERY(
                           p_api_version_number => 1.0,
                           p_init_msg_list => l_init_msg_list,
                           x_return_status => xx_return_status,
                           x_msg_count => xx_msg_count,
                           x_msg_data => xx_msg_data,
                           p_action_code => 'UPDATE',
                           p_delivery_info => x_delivery_info,
                           x_delivery_id => x_delivery_number,
                           x_name => x_delivery_name);


I been working with Oracles APIs for quite some time and kinda, got used to the fact that ATTRIBUTE_CATEGORY update is "built in" (I been told its mater of the setup though).

Anyway, in this particular case YOU MUST provide the value to the record to have it working correctly:

x_delivery_info.attribute_category := fnd_profile.value('ORG_ID');


Hopefully 2 minutes of reading   save you some time.

Take care,

Why nothing is fetched from GMD_RECIPES_B?

hi All,

This is my first post, YES SIR!
The only reason I didn't post anything so far- no bugs left in Oracle what so ever...LOL.
Seriously, there are plenty of those to go around, yet for the most I came across, there are already solutions/work-arounds published somewhere.
And besides, who am I to decide what is a BUG and what is a FEATURE? :)
So basically, I am going to gather here info that might be not necessarily genuine, yet might have and additional info to supplement previous posts - needless to remind not everybody has account at Metalink.

As for the issue previously mentioned:

Problem: You are able to access Formulas and Recipes  at  Oracle EBS - via several Product Manufacturing Responsibilities, but nothing is ever fetched by SQL*Plus/PL/SQL Developer/TOAD - you name it, no matter what FND_LOBAL.APPS_INITIALIZE() you been trying. 

Solution: I am mainly quoting solution already posted on ONT Forum, though my is rather shorter:
Assign the Oracle EBS User you been using for APPS_INITIALIZE the Formulator Responsibility and here you go!
Link to original post:
https://forums.oracle.com/forums/thread.jspa?threadID=2230436