Hi

Somebody please help me for executing below sql query, need baan erp syntax for Inner Join and Left outer join



SELECT tdpur200.rqno , tdpur200.rdat, tdpur201.item ,
tdpur201.nids ,
tdpur201.qoor , tdpur202.prno, tdpur400.odat,
tdpur400.otbp ,
tdpur401.qoor , tdpur400.hdst
FROM tdpur200 INNER JOIN
tdpur201 ON tdpur200.rqno = tdpur201.rqno LEFT OUTER JOIN
tdpur401 INNER JOIN
tdpur400 ON tdpur401.orno = tdpur400.orno INNER JOIN
tdpur202 ON tdpur401.orno = tdpur202.prno AND
tdpur401.pono = tdpur202.ppon AND
tdpur401.sqnb = tdpur202.sqnb ON tdpur201.rqno =
tdpur202.rqno AND
tdpur201.pono = tdpur202.pono
selectdo
rprt_send()
endselect

select tdpur200.*,tdpur201.*,tdpur202.*,tdpur401.*,
tcibd001.kitm
from tdpur200,tdpur201,tdpur202,tdpur401,
tcibd001
where tdpur200._index1 inrange {:rqno.f}
and {:rqno.t}
and tdpur201._index1 = {tdpur200.rqno}
| above one is inner join
and {tdpur200.remn,tdpur200.rdat,tdpur200.rqst} inrange {:remn.f,:rdat.f,:rqst.f}
and {:remn.t,:rdat.t,:rqst.t}
and tdpur201.cnty <> tdpur.cnty.rfq
| below one is left outer join
and tdpur201._index1 refers to tdpur202 UNREF CLEAR
and tdpur202._index2 refers to tdpur401 UNREF CLEAR
and tdpur201.item refers to tcibd001
order by tdpur200._index1
selectdo

Hi Vamsi,

Thanks for the reply, i tried to use below program script and getting below issues,

a) All Requisition lines are not coming,
b) Next requisition is taking order details of previous requisition line infact no PO generated for requisition
c) Need to add one more column, where pending qty of requisition lines to come

Plz help


|****************************** declaration section ***************************
declaration:

table ttdpur200 | Requisition
table ttdpur201 | Requisition Lines
table ttdpur202 | Link between Requisition & Order
table ttdpur400 | Purchase Order
table ttdpur401 | Purchase Order Lines
table ttcibd001 | Item General
table ttccom100 | Business Partner

extern domain tcrqno rqno.f,rqno.t | Requisition No
extern domain tcdate rdat.f,rdat.t | Requistion Date


|****************************** group section **********************************

group.1:
init.group:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif


|****************************** field section *********************************

field.rqno.f:
when.field.changes:
rqno.t = rqno.f
field.rdat.f:
when.field.changes:
rdat.t = rdat.f

|****************************** program section ********************************


|****************************** function section ******************************

functions:

function read.main.table()
{
SELECT tdpur200.*
FROM tdpur200
where tdpur200._index1 inrange {:rqno.f} and {:rqno.t}
and tdpur200.rdat inrange :rdat.f and :rdat.t
selectdo
select tdpur201.item, tdpur201.nids, tdpur201.qoor
from tdpur201
where tdpur201.rqno = :tdpur200.rqno
selectdo
select tdpur202.prno
from tdpur202
where tdpur202.rqno = :tdpur200.rqno
selectdo
endselect
|select tdpur400.odat, tdpur400.otbp, tdpur400.hdst
select tdpur400.*
from tdpur400
where tdpur400.orno = :tdpur202.prno
selectdo
select tdpur401.*
from tdpur401
where tdpur401.orno = :tdpur400.orno
selectdo
endselect
select tccom100.*
from tccom100
where tccom100.bpid = :tdpur400.otbp
selectdo
endselect
endselect

endselect
rprt_send()
endselect
}

Hi,

Try this piece of code -

|****************************** declaration section ***************************
declaration:

table ttdpur200 | Requisition
table ttdpur201 | Requisition Lines
table ttdpur202 | Link between Requisition & Order
table ttdpur400 | Purchase Order
table ttdpur401 | Purchase Order Lines
table ttcibd001 | Item General
table ttccom100 | Business Partner

extern domain tcrqno rqno.f,rqno.t | Requisition No
extern domain tcdate rdat.f,rdat.t | Requistion Date
extern domain tcqiv1 tot.pend,tot.conv


|****************************** group section **********************************

group.1:
init.group:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif


|****************************** field section *********************************

field.rqno.f:
when.field.changes:
rqno.t = rqno.f
field.rdat.f:
when.field.changes:
rdat.t = rdat.f

|****************************** program section ********************************


|****************************** function section ******************************

functions:

function read.main.table()
{

select tdpur200.*,tdpur201.*
from tdpur200,tdpur201
where tdpur201._index1 inrange {:rqno.f} and {:rqno.t}
and tdpur201.dldt inrange :rdat.f and :rdat.t
and tdpur201._index1 refers to tdpur200
selectdo
db.set.to.default(ttdpur202)
db.set.to.default(ttdpur400)
db.set.to.default(ttdpur401)
db.set.to.default(ttccom100)
total.converted.quantity = 0 |# Quantity Converted to Purchase Order
select tdpur202.prno
from tdpur202
where tdpur202.rqno = :tdpur200.rqno
selectdo
db.set.to.default(ttdpur400)
db.set.to.default(ttdpur401)
db.set.to.default(ttccom100)
select tdpur400.*,tdpur401.*
from tdpur400,tdpur401
where tdpur401._index1 = {:tdpur202.prno,:tdpur202.ppon,:tdpur202.sqnb}
and tdpur401._index1 refers to tdpur400
selectdo
db.set.to.default(ttccom100)
select tccom100.*
from tccom100
where tccom100.bpid = :tdpur400.otbp
selectdo
endselect
tot.conv = tot.conv + tdpur401.qidl
endselect
endselect

tot.pen = tdpur201.qoor - tot.conv |#Total Pending = Requested - Received Quantity

rprt_send()
endselect
}

Hello Mr. Bhushan

With below code, i am able to generate my data completely correct, but got stuck in little issue as item requisitions lines against which purchase order not created, that lines are showing order status as created. In report on order status field, i am printing tdpur400.hdst. Please help


|****************************** declaration section ***************************
declaration:

table ttdpur200 | Requisition
table ttdpur201 | Requisition Lines
table ttdpur202 | Link between Requisition & Order
table ttdpur400 | Purchase Order
table ttdpur401 | Purchase Order Lines
table ttcibd001 | Item General
table ttccom100 | Business Partner

extern domain tcrqno rqno.f,rqno.t | Requisition No
extern domain tcdate rdat.f,rdat.t | Requistion Date
extern domain tcqiv1 tot.pend


|****************************** group section **********************************

group.1:
init.group:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif


|****************************** field section *********************************

field.rqno.f:
when.field.changes:
rqno.t = rqno.f
field.rdat.f:
when.field.changes:
rdat.t = rdat.f

|****************************** program section ********************************


|****************************** function section ******************************

functions:

function read.main.table()
{
select tdpur200.*,tdpur201.*
from tdpur200,tdpur201
where tdpur201._index1 inrange {:rqno.f} and {:rqno.t}
and tdpur201.dldt inrange :rdat.f and :rdat.t
and tdpur201._index1 refers to tdpur200
selectdo
db.set.to.default(ttdpur202)
db.set.to.default(ttdpur400)
db.set.to.default(ttdpur401)
db.set.to.default(ttccom100)
select tdpur202.prno
from tdpur202
where tdpur202.rqno = :tdpur201.rqno
and tdpur202.pono = :tdpur201.pono
selectdo
select tdpur400.*
from tdpur400
where tdpur400.orno = :tdpur202.prno
selectdo
select tdpur401.*
from tdpur401
where tdpur401.orno = :tdpur400.orno
and tdpur401.orno = :tdpur202.prno
|and tdpur401.pono = :tdpur202.pono
selectdo
select tccom100.*
from tccom100
where tccom100.bpid = :tdpur400.otbp
selectdo
endselect
selectempty
tdpur400.hdst = tdpur.hdst.not.applicable
endselect
endselect
endselect
tot.pend = tdpur201.qoor - tdpur401.qoor |#Total Pending = Indent Qty - Ordered Qty
rprt_send()
endselect
}

Hi,

Go to your report layout, double click on the status field and under Conditions tab, there is a field named Print Condition. Write the following there -

not isspace(tdpur400.orno)

And, now you are good to go. The purchase order status will not print if the order number is blank.

in that case .. tdpur202.prno should be blank i guess ,

Print condition of tdpur400.hdst : not isspace(tdpur202.prno)

thanks for the help, my report completed.