display item month by month for the specific item, respectively

thanks...it work~!!!

Iwadwell, i have a question want to ask u, hope u can help me~! thanks.....

SELECT a.customer_item_name, a.item_description, b.item_cost,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity, 0)) AS January,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity, 0)) AS February,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '03', a.Quantity, 0)) AS March,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '04', a.Quantity, 0)) AS April,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '05', a.Quantity, 0)) AS Mei,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '06', a.Quantity, 0)) AS Jun,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '07', a.Quantity, 0)) AS July,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '08', a.Quantity, 0)) AS August,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '09', a.Quantity, 0)) AS September,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '10', a.Quantity, 0)) AS October,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '11', a.Quantity, 0)) AS November,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '12', a.Quantity, 0)) AS December,
       sum(Quantity) AS Total_Item
FROM msc_sup_dem_entries@APS1159 a, cst_item_cost_details_v b
WHERE a.publisher_order_type_desc like 'Order forecast'
AND TO_CHAR(a.Receipt_Date, 'YYYY') = '2004'
AND a.inventory_item_id = b.inventory_item_id
AND b.cost_type_id = 1
GROUP BY a.customer_item_name, a.item_description, b.item_cost

why it came out the duplicate CUSTOMER_ITEM_NAME and duplicate ITEM_COST after i add-in the B.ITEM_COST~!???? is there anything wrong with the script~!??  Before of doing that, it just display a single's customer_item_name and the total month's quantity for the specific month only~!!! What i want to get is, i want to display out the item cost (unit price) for the specific item only~!!!

CUSTOMER_ITEM_NAME      ITEM_DESCRIPTION     ITEM_COST     JANUARY........
01000601TJM                       ABCDEF                          0.3658           20158

ne_ready,

GROUP BY does what the name might suggest, groups the results into the distinct combinations that exist in the group by list.  When you have three columns in the group by, all three columns must be the same before they can be considered as 'duplicate'.  I doubt that Oracle is doing this in your case...something on each returned row for 'a.customer_item_name, a.item_description, b.item_cost' must differ from all other rows.  For example...

CUSTOMER_ITEM_NAME      ITEM_DESCRIPTION     ITEM_COST     JANUARY........
01000601TJM             ABCDEF               0.3658        20158
01000601TJM             ABCDEF               0.4           99

are not duplicates because the ITEM_COST values differ (0.3658 vs 0.4).  Or...

CUSTOMER_ITEM_NAME      ITEM_DESCRIPTION     ITEM_COST     JANUARY........
01000601TJM             ABCDEF               0.3658        20158
01000601TJM             ZYXWVU               0.3658        99

are not duplicates because the ITEM_DESCRIPTION values differ (ABCDEF vs ZYXWVU).


I don't have enough information to suggest a solution yet...I need to understand the relationship between the two tables and the various columns.  You are grouping by customer_item_name yet you are joining using inventory_item_id, are these 1:1?

I need to understand the data much more...

Iwadwell, i knew the answer, because it have a different cost_element on it, some of them are duplicated~!!  :-)
So, how do i calc the item_cost (unit price) * quantity follow with month by month basic~!??? Do i need to declare the variable something like -> total  number(15)~???


CUSTOMER_ITEM_NAME      ITEM_COST       JANUARY      Total.............Dec
01001931MMTF                    0.236                 2500            590

Hope u can help me, cause i very new in PL/SQL~!!!

what do you mean by "duplicated"...?  I am assuming this is two rows in the cst_item_cost_details_v table with the same inventory_item_id.  Please provide an example...it might help me understand better.  Is the item_cost the same in each row?...if so, maybe just doing a max(item_cost) is all you need to do...

ops.....sorry~!!! Actually inside the table, it has a column called COST_ELEMENT, and it is categorize as follow : Material, Resource and so on....for the specific item_cost, respectively~! Now, i just want to select out those ITEM_COST which is based on Material inside the COST_ELEMENT. Finally, i wanna do a calculation of each month~!!

CUSTOMER_ITEM_NAME      ITEM_COST       JANUARY      Total       February     Total
01001931MMTF                    0.236                 2500            590          1458        344.088
010A0016801Q                     0.36                   258             92.88        xxxx          xxxx

ok...lets see if I understand what you are saying...

the cst_item_cost_details_v has multiple rows, with differing COST_ELEMENT values.

in short, you need to be able to join to one, and only one, row in the CST_ITEM_COST_DETAILS_V table for each value of INVENTORY_ITEM_ID.  You need to tell me how this can be done...?  Or is this the sum(item_cost) for these rows...?

if the following SQL returns rows, then we have a problem...

select INVENTORY_ITEM_ID
from   cst_item_cost_details_v
where  cost_type_id = 1
and    cost_element = 'Material' -- or whatever this is required to be to only select material costs...
group by INVENTORY_ITEM_ID
having count(*) > 1

as to calculation, once we have the above sorted out, this is simple.

Well, inside the cst_item_cost_details_v, it have same inventory-item_id, due to different item_cost and cost_element~! And now, i just retrieved the item_cost which is based on Material  under the cost_element~! So, now i get the single inventory_item_id and item_cost.

But how can i add the script to calc the quantity x item_cost per monthly basic based on the script u guided me~????

CUSTOMER_ITEM_NAME      ITEM_COST       JANUARY      Total       February     Total
01001931MMTF                    0.236                 2500            590          1458        344.088
010A0016801Q                     0.36                   258             92.88        xxxx          xxxx

for example: 0.236 x 2500 = 590

just multiply them out...either as...(again just a few for an example)...

       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity, 0)) AS Jan_qty,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity * item_cost, 0)) AS Jan_amt,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity, 0)) AS Feb_qty,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity * item_cost, 0)) AS Feb_amt,

or

       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity, 0)) AS Jan_qty,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity, 0))
            * item_cost                                                AS Jan_amt,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity, 0)) AS Feb_qty,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity, 0))
            * item_cost                                                AS Feb_amt,

Iwadwell, thank for your help~!!!

that's ok...glad to be of help.

Hey, i would like to creata a parameter form and run a query inside it, For example, i have many items -> e.g "MC-2.2MFD-10V-K-X5R-0805-TAP-X", "FAC-100MFD-25V-M-105C-6.3*7-DIP-TAP-X
" inside the item_description and I just want to key-in the first fews char either MC or FAC  and it will only come out those records that it's item name is start from MC or FAC. How to do that?
Well...from designing the report -> Data Model ->Layout Model're all fine, but i just wonder what's wrong in designing the parameter form. I need the correct steps and the appropriate script to make a query inside my parameter form.

The steps what i'd been done were:
1. Data Model add:
   AND cost_element like 'Material'
   &p_item_desc
   GROUP BY a.inventory_item_id, a.customer_item_name, a.item_description, b.item_cost

2. Parameter Model add:
   source = p_item_desc
   datatype = character
   width = 500

   object navigator (report triggers):
   before report = rightclick - PL/SQL Editor add:
   :p_item_desc := 'and a.item_description like ';

I get the error msg : ORA-00933 SQL command not properly ended~!!!

Hi, i would like to do a counting of suppliers, they are AMP, MURATA (US), MURATA(IPC)-USD, TI-VMI. I can get the total of suppliers, but i not know how to get the specific count total of each supplier.

here the script:

select count(pvs.vendor_site_code) as vendors
from mtl_system_items msi,
mtl_item_categories mic,
mtl_categories mc,
mtl_item_categories mic1,
mtl_categories mc1,
mtl_item_categories mic2,
mtl_categories mc2,
mtl_mfg_part_numbers mmpn,
po_asl_attributes paa,
po_approved_supplier_list pasl,
per_people_f ppf,
po_vendors pv,
po_vendor_sites_all pvs,
mrp_sr_source_org msso,
mrp_sourcing_rules msr,
mrp_sr_receipt_org msro,
org_organization_definitions ood
where msi.organization_id = :p_org_id
and msi.organization_id = ood.organization_id
and msi.inventory_item_status_code != 'Inactive'
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and msi.inventory_item_id = mic1.inventory_item_id
and msi.organization_id = mic1.organization_id
and msi.inventory_item_id = mic2.inventory_item_id(+)
and msi.organization_id = mic2.organization_id(+)
and msi.segment1 like '0%'
and mic.category_set_id = 7
and mic1.category_set_id = 6
and mic2.category_set_id(+) = 1100000001
and mic.category_id = mc.category_id
and mic1.category_id = mc1.category_id
and mic2.category_id = mc2.category_id(+)
and mc.segment1 = 'RM'
and msi.inventory_item_id = mmpn.inventory_item_id(+)
and msi.inventory_item_id = paa.item_id
and paa.using_organization_id = -1
and paa.using_organization_id = pasl.using_organization_id
and paa.asl_id = pasl.asl_id
and msi.organization_id = pasl.owning_organization_id
and pasl.vendor_id = paa.vendor_id
and pasl.vendor_site_id = paa.vendor_site_id
and paa.item_id = pasl.item_id
and nvl(pasl.disable_flag,'N') != 'Y'
and msso.vendor_id = pv.vendor_id
and pvs.vendor_id = pv.vendor_id
and msso.vendor_site_id = pvs.vendor_site_id
and msso.sr_receipt_id = msro.sr_receipt_id
and msr.sourcing_rule_id = msro.sourcing_rule_id
and msr.organization_id = msi.organization_id
and msr.sourcing_rule_name = msi.segment1
and msro.disable_date is null
and paa.vendor_id = pv.vendor_id
and pvs.vendor_site_code in ('AMP','MURATA (US)','MURATA(IPC)-USD','TI-VMI')
and msi.buyer_id = ppf.person_id(+)
and ppf.full_name != 'TAH, Ms.'
order by msi.segment1