Home » SQL & PL/SQL » SQL & PL/SQL » procedure
procedure [message #267287] Wed, 12 September 2007 23:56 Go to next message
ksankar
Messages: 32
Registered: September 2007
Member
hi,

I have some problem in procudure.i prepared on procudre for move the data from local tables to interface tables.
after i run this procudure data will move to local tables to two interfacetables.
my requirement is how many org_ids (distinct) exist in the local table (b.org_id) that much times rcv_headers_interface must repeate.

for details query pls check the attached file.
for any clarification pls let me know .

Pls help to me.

Thanks,
Sankar.K
Re: procedure [message #267290 is a reply to message #267287] Wed, 12 September 2007 23:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I don't understand the question/problem.
Re: procedure [message #267294 is a reply to message #267290] Thu, 13 September 2007 00:07 Go to previous messageGo to next message
ksankar
Messages: 32
Registered: September 2007
Member
anacedent,

pls check the procedure, there you can identify the one columne (org_id) in rcv_grn_line table.
if org_id is morethan one in the sence, count(org_id) times data is need to insert into rcv_headers_interface table.

pls check procudure and let me know any clarification.

Thanks,
Sankar
Re: procedure [message #267304 is a reply to message #267287] Thu, 13 September 2007 00:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
OK, color me a dense dolt who can not read & understand the problem statement;
therefore I can't help & Your On Your Own (YOYO)!
Re: procedure [message #267336 is a reply to message #267294] Thu, 13 September 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree with Ana.
320 lines procedure without any clear explaination, table description and so on => YOYO

Regards
Michel
Re: procedure [message #267344 is a reply to message #267336] Thu, 13 September 2007 01:32 Go to previous messageGo to next message
ksankar
Messages: 32
Registered: September 2007
Member
Michel,

local tables details

rcv_grn_header primary key grn_header_id
rcv_grn_line foreign key grn_no

CURSOR cur_grn_new
IS
SELECT a.GRN_HEADER_ID,
a.SUPPLIER,
B.DESCRIPTION,
a.PO_NO,
b.UOM,
b.RCV_QTY,
b.PO_LINE_ID,
b.ORG_ID,
a.DC_NO,
a.DC_DATE,
a.NO_OF_PACKS,
a.NET_WT,
a.INVOICE_NO,
a.INVOICE_DATE ,
a.INVOICE_CURRENCY,
a.INVOICE_AMOUNT,
a.RCV_DATE arrival_dt,
a.DELIVERED_DATE grn_signed_date,
a.DELIVERED_DATE fin_date,
DECODE(a.ATTRIBUTE2,1,'Local','Imported'),
a.STORAGE_LOC Location,
c.CHA_NAME,
a.CT3_NO,
a.DUTY_FORGONE,
a.BOE_NO,
a.BOE_DATE,
d.TYPE_DESCIPTION,
a.CIF_VALUE,
a.DOCS_RCV_DATE,
a.CLEARED_DATE,
a.LANDING_DATE ,
e.AGENT_NAME,
a.BOND_NO ,
a.BOND_DATE ,
a.BOND_EXP_DATE,
a.MODE_ID,
a.BOE_TYPE
FROM RCV_GRN_HEADER a,
RCV_GRN_LINE b,
RCV_CHA c,
RCV_GOODS_TYPE D,
RCV_GRN_STEAMER_AGENT E
WHERE a.ATTRIBUTE1 = 2
AND a.DELIVERED_DATE IS NOT NULL
AND a.GRN_STATUS <> 'Cancelled'
AND a.GRN_HEADER_ID = b.GRN_NO
AND a.CHA_ID = c.CHA_ID(+)
AND a.UPLOAD_FLAG = 1
AND d.TYPE_ID(+) = a.TYPE_OF_GOODS
AND e.AGENT_NO(+) = a.STAEMER_AGENT_NO
AND a.location = '1'
AND b.RCV_QTY <> 0

if this query is fetching any rows it is going to insert to
rcv_headers_interface and rcv_transaction_interface.

b.org_id is inserting to rcv_headers_interface.ship_to_organization_id
here my problem is b.org_id is more than one also, it is inserting one row only that to first org_id is only it is taking,it is not taking sencond org_id.


Re: procedure [message #267351 is a reply to message #267344] Thu, 13 September 2007 01:43 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: 2 face commit on two diffirent databases
Next Topic: procedure
Goto Forum:
  


Current Time: Thu Dec 08 20:12:19 CST 2016

Total time taken to generate the page: 0.41923 seconds