第二段SQL Query内容比较多
SELECTDECODE(:P_SORT, 'VENDOR', pov.vendor_name, 'BUYER', hre.full_name, pov.vendor_name)
break_group_column
,
DECODE(:P_SORT, 'VENDOR', hre.full_name, 'BUYER', pov.vendor_name,hre.full_name )
other_group_column,
poh.segment1||decode(por.release_num,null,'','-'||por.release_num)PO_Number,
decode( mod(glp.period_num,12), mod(:P_period_num,12),
/**/
decode(poh.closed_code,
'FINALLY CLOSED',
SUM (DECODE (POL.order_type_lookup_code,
'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
'FIXED PRICE',GREATEST (PLL.amount_received, PLL.amount_billed),
GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
SUM (DECODE (POL.order_type_lookup_code,
'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
)
C_AMOUNT_PER1,
decode( mod(glp.period_num,12), mod(:P_period_num + 1,12),
/**/
decode(poh.closed_code,
'FINALLY CLOSED',
SUM (DECODE (POL.order_type_lookup_code,
'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
'FIXED PRICE',GREATEST (PLL.amount_received, PLL.amount_billed),
GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
SUM (DECODE (POL.order_type_lookup_code,
'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
)
C_AMOUNT_PER2,
decode( mod(glp.period_num,12), mod(:P_period_num + 2,12),
/**/
decode(poh.closed_code,
'FINALLY CLOSED',
SUM (DECODE (POL.order_type_lookup_code,
'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
'FIXED PRICE',GREATEST (PLL.amount_received, PLL.amount_billed),
GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
SUM (DECODE (POL.order_type_lookup_code,
'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
)
C_AMOUNT_PER3,
decode( mod(glp.period_num,12), mod(:P_period_num + 3,12),
/**/
decode(poh.closed_code,
'FINALLY CLOSED',
SUM (DECODE (POL.order_type_lookup_code,
'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
'FIXED PRICE',GREATEST (PLL.amount_received, PLL.amount_billed),
GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
SUM (DECODE (POL.order_type_lookup_code,
'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1),
mod(:P_period_num + 4,12),
decode(poh.closed_code,
'FINALLY CLOSED',
SUM (DECODE (POL.order_type_lookup_code,
'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
'FIXED PRICE',GREATEST (PLL.amount_received, PLL.amount_billed),
GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
SUM (DECODE (POL.order_type_lookup_code,
'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1),
mod(:P_period_num + 5,12),
decode(poh.closed_code,
'FINALLY CLOSED',
SUM (DECODE (POL.order_type_lookup_code,
'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
'FIXED PRICE',GREATEST (PLL.amount_received, PLL.amount_billed),
GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
SUM (DECODE (POL.order_type_lookup_code,
'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
)
C_AMOUNT_PER4,
mca.segment1seg1
FROM po_line_locations pll
,po_lines
pol
,po_releases por
,po_headerspoh
,po_vendorspov
,hr_employeeshre
,gl_period_statusesglp
,financials_system_parameters fsp
,po_system_parameters psp
,mtl_categories mca
WHEREpoh.po_header_id= pll.po_header_id
ANDpor.po_release_id (+) = pll.po_release_id
ANDpll.po_line_id =pol.po_line_id
ANDpol.category_id =mca.category_id
ANDpoh.vendor_id = pov.vendor_id
ANDhre.employee_id = poh.agent_id
ANDnvl(nvl(pll.promised_date,pll.need_by_date),sysdate)
BETWEENnvl(glp.start_date, nvl(nvl(pll.promised_date,pll.need_by_date),sysdate) - 1 )
ANDnvl(glp.end_date, nvl(nvl(pll.promised_date,pll.need_by_date),sysdate) + 1)
ANDapplication_id = 201
ANDglp.set_of_books_id = fsp.set_of_books_id
AND(( (glp.period_num
BETWEEN:P_period_numAND decode ( sign (((:P_period_num +5)/12)-1), -1, :P_period_num+5, 12))
ANDglp.period_year = :P_period_year)
OR
((glp.period_num
BETWEEN1 AND decode ( sign (((:P_period_num+5)/12)-1), -1,0, mod(:P_period_num+5,12)))
AND glp.period_year = :P_period_year + 1))
ANDpll.shipment_type in ('STANDARD', 'SCHEDULED', 'BLANKET')
ANDpov.vendor_name
BETWEENnvl(:P_vendor_from,pov.vendor_name) AND nvl(:P_vendor_to, pov.vendor_name)
AND((decode(psp.manual_po_num_type,'NUMERIC',poh.segment1,NULL)
BETWEEN decode(psp.manual_po_num_type,'NUMERIC',
to_number(nvl(:P_po_number_from, poh.segment1)),NULL)
AND decode(psp.manual_po_num_type,'NUMERIC',
to_number(nvl(:P_po_number_to, poh.segment1)),NULL))
OR (poh.segment1
BETWEEN decode(psp.manual_po_num_type,'ALPHANUMERIC',
nvl(:P_po_number_from,poh.segment1),NULL)
AND decode(psp.manual_po_num_type,'ALPHANUMERIC',
nvl(:P_po_number_to,poh.segment1),NULL)))
/* ANDhre.full_name = nvl(:P_buyer, full_name) */
/* Bug#2453022 Commented out the above condition and replaced
it as follows as the user parameter P_Buyer will now return the id and not the name */
ANDhre.employee_id = nvl(:P_buyer, hre.employee_id)
/**/
AND ( ( NVL(POH.closed_code, 'OPEN')'FINALLY CLOSED'
AND
( DECODE(POL.order_type_lookup_code,
'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
PLL.quantity - NVL(PLL.quantity_cancelled,0)) > 0)
)
OR
( POH.closed_code = 'FINALLY CLOSED'
AND
( DECODE(POL.order_type_lookup_code,
'RATE', NVL(GREATEST (PLL.amount_received, PLL.amount_billed), 0),
'FIXED PRICE', NVL(GREATEST (PLL.amount_received, PLL.amount_billed), 0),
NVL(GREATEST (PLL.quantity_received,PLL.quantity_billed), 0)) > 0)
)
)
AND ( DECODE (POL.order_type_lookup_code,
'RATE',
PLL.amount - NVL(PLL.amount_cancelled, 0) -
NVL( GREATEST (PLL.amount_received, PLL.amount_billed), 0),
'FIXED PRICE',
PLL.amount - NVL(PLL.amount_cancelled, 0) -
NVL( GREATEST (PLL.amount_received, PLL.amount_billed), 0),
PLL.quantity - NVL(PLL.quantity_cancelled, 0) -
NVL( GREATEST (PLL.quantity_received, PLL.quantity_billed), 0)) > 0)
/**/
AND&P_WHERE_CAT
GROUP BY
pov.vendor_name
,hre.full_name
,poh.segment1||decode(por.release_num,null,'','-'||por.release_num)
,glp.period_year,glp.period_num
,glp.period_year,glp.period_num
,glp.period_year,glp.period_num
,glp.period_year, glp.period_num
,decode(psp.manual_po_num_type,'NUMERIC',
to_number(poh.segment1),NULL),
decode(psp.manual_po_num_type,'NUMERIC',
NULL,poh.segment1),
por.release_num ,
mca.segment1 ,
poh.closed_code,
NVL(poh.rate, 1)
ORDER BY decode(psp.manual_po_num_type,'NUMERIC',
to_number(poh.segment1),NULL),
decode(psp.manual_po_num_type,'NUMERIC',
NULL,poh.segment1),
por.release_num
这段看起来好像很复杂，其实还是比较容易理解的，例如mod(:P_period_num + 1,12),就是系统在计算你选择期间之后的5个期间的金额。
至于是那些状态的PO，程式也看了一下，就是先抓去Finally Close的采购单
decode(poh.closed_code,
'FINALLY CLOSED',
SUM (DECODE (POL.order_type_lookup_code,
'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
'FIXED PRICE',GREATEST (PLL.amount_received, PLL.amount_billed),
GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
如果不是该状态的，就抓取
SUM (DECODE (POL.order_type_lookup_code,
'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
)
|
