Home » SQL & PL/SQL » SQL & PL/SQL » need help in writing pl/sql script
need help in writing pl/sql script [message #303044] Wed, 27 February 2008 14:33 Go to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
Hi,

I need to write pl/sql script to do this step.pl help me.

a. NFMAPROD change : New table XXNFMA_PPS_PRODUCTS to hold PPS information and UNSPSC information
• Data will transferred from EDW_PPS_PRODUCTS to this new table
• Based upon Calculation rules specified in the Appendix, packaging levels will be calculated
• The calculated packaging information will then be pivoted into the designated rows and columns in the table XXNFMA_PPS_PACKAGING
  • Attachment: pps.sql
    (Size: 1.45KB, Downloaded 160 times)
Re: need help in writing pl/sql script [message #303139 is a reply to message #303044] Thu, 28 February 2008 02:15 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Provide a test case i.e. provide create table and insert scripts that will allow us to build a representative set of data. Provide what you have tried. Provide what you expect the data (as you have supplied) to do. Do not over simplify, but do not include more info than is necessary
Re: need help in writing pl/sql script [message #303237 is a reply to message #303139] Thu, 28 February 2008 08:55 Go to previous messageGo to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
Hi,

I am not sure which information do I need to submit but pl find the example data as follows..

From OCS
Level From Conv To
1 CA 1 CA
2 BX 5 CA
3 EA 10 BX

OCS Slotted
Outer Outer_Inter Outer_UoU Outer_Sub Inter Inter_UoU Inter_Sub UoU UoU_Sub Sub
CA 5 BX 10 EA

In PPS
Outer Outer_Inter Outer_UoU Outer_Sub Inter Inter_UoU Inter_Sub UoU UoU_Sub Sub
PK 10 EA

New PPS
Outer Outer_Inter Outer_UoU Outer_Sub Inter Inter_UoU Inter_Sub UoU UoU_Sub Sub
CA 5 BX 10 EA

Sync View
Outer Outer_Inter Outer_UoU Outer_Sub Inter Inter_UoU Inter_Sub UoU UoU_Sub Sub
CA 5 BX 10 EA

To Catalog
Level From Conv To
1 CA 1 CA
2 BX 5 CA
3 EA 10 BX



Re: need help in writing pl/sql script [message #303240 is a reply to message #303237] Thu, 28 February 2008 09:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I am not sure which information do I need to submit but pl find the example data as follows..



You need to submit create table and insert scripts like

create table ....


and

insert into ....


and use code tags so that it readable. In your post it is impossible to see which data is in which column.
Re: need help in writing pl/sql script [message #303245 is a reply to message #303240] Thu, 28 February 2008 09:14 Go to previous messageGo to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
Hi thomas,

Pl find the data that should be loaded like this ..



Level From_uom Conversion_factor To_uom
1 CA 1 CA
2 BX 5 CA
3 EA 10 BX

Does it helpful to understand to u.pl let me know..

Thank you for helping me.
Re: need help in writing pl/sql script [message #303251 is a reply to message #303245] Thu, 28 February 2008 09:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you actually READ the replies you get?
Re: need help in writing pl/sql script [message #303260 is a reply to message #303251] Thu, 28 February 2008 09:45 Go to previous messageGo to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
I read the replies .so let me know where I am going wrong.
Re: need help in writing pl/sql script [message #303264 is a reply to message #303260] Thu, 28 February 2008 09:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You still haven't posted the create and insert statements pablolee and I asked for, and you still don't use code tags, so I doubt that you really read the replies.

Without a test case I can not help you.

Also, the requirement is pretty unclear. Maybe because we still can't see which data is actually in what tables/columns.
Re: need help in writing pl/sql script [message #303266 is a reply to message #303260] Thu, 28 February 2008 09:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
To help you, we may need to reproduce the case in our environment.
To reproduce the case, we need the table (create table statements),
data (insert into table statements) and anything else to reproduce
the issue.
Here is an example for what folks expect here to reproduce the case.
http://www.orafaq.com/forum/m/298612/42800/#msg_298612
Re: need help in writing pl/sql script [message #303269 is a reply to message #303266] Thu, 28 February 2008 10:05 Go to previous messageGo to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
Hi Mahesh,

I am creating table like this
CREATE TABLE XXNFMA_PPS_PACKAGING(
PIM_KEY INT,
OCS_PRODUCT_KEY INT,
PACKAGING_LEVEL INT,
FROM_UOM VARCHAR2(30),
CONVERSION_FACTOR INT,
TO_UOM VARCHAR2(30));



and I am not sure how to insert data into this table.
Re: need help in writing pl/sql script [message #303301 is a reply to message #303269] Thu, 28 February 2008 16:15 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd try with something like
INSERT INTO xxnfma_pps_packaging
  (pim_key, ocs_product_key, packaging_level, from_uom, conversion_factor, to_uom)
   values
  (1, 123, 443, 'Little', 32, 'Foot');

INSERT INTO xxnfma_pps_packaging
  (pim_key, ocs_product_key, packaging_level, from_uom, conversion_factor, to_uom)
   values
  (2, 42423, 1255, 'Scott', 882, 'Tiger');

etc.
Re: need help in writing pl/sql script [message #303468 is a reply to message #303269] Fri, 29 February 2008 08:11 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
balakrishnausa wrote on Thu, 28 February 2008 11:05
Hi
and I am not sure how to insert data into this table.



You may want to look at the Oracle documentation for the INSERT command.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698
Re: need help in writing pl/sql script [message #303476 is a reply to message #303237] Fri, 29 February 2008 08:38 Go to previous messageGo to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
hi ,

How to delete my post.

[Updated on: Fri, 29 February 2008 08:42]

Report message to a moderator

Re: need help in writing pl/sql script [message #303484 is a reply to message #303476] Fri, 29 February 2008 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to delete your post and which one?

Regards
Michel
Re: need help in writing pl/sql script [message #303549 is a reply to message #303484] Fri, 29 February 2008 14:05 Go to previous messageGo to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
hi,

i would like to post it again with full information so delete my all previous posts
Re: need help in writing pl/sql script [message #303550 is a reply to message #303549] Fri, 29 February 2008 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not matter, post the whole information now.

Regards
Michel
Re: need help in writing pl/sql script [message #303587 is a reply to message #303550] Fri, 29 February 2008 23:13 Go to previous messageGo to next message
balakrishnausa
Messages: 9
Registered: February 2008
Junior Member
hi mike,

I already posted it .Can u delete this entire tree now.
Re: need help in writing pl/sql script [message #303590 is a reply to message #303044] Fri, 29 February 2008 23:31 Go to previous message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
It does not matter.




you have posted it in new thread.so reply to that thread .we are waitinng for your response to watch your problem in prescribed format.

regards,

[Updated on: Fri, 29 February 2008 23:32]

Report message to a moderator

Previous Topic: Performing DML operations on Pipelined functions
Next Topic: How to export objects from Access 2003 to oracle 11g ?
Goto Forum:
  


Current Time: Sat Dec 03 16:29:24 CST 2016

Total time taken to generate the page: 0.06247 seconds