Applications Report Error - Business Intelligence Applications

Hi,
There is an SQL Plus Report which was developed by someone else a few years ago. And now i pulled the code to make a few changes in the columns and put it back.
Before i made the change i ran the report from the Oracle applications and there was no error.
But for some reason when i changed it and ran it from applications, there is an error that shows up:
Another thing: when we put back the original, the same error comes through.
This runs fine in the Unix box, and TOAD.
EXEC FND_CONC_STAT.COLLECT
*
ERROR at line 163:
ORA-00933: SQL command not properly ended
And so i reverted back to the original code and the same error shows up.
Can someone tell me why this is error is showing up?
This is the sql code for the report:
-------------------------------------------------------------------------------------------------------------------------********************************************************************************************
SET ECHO OFF
SET SHOW OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 450
--WHENEVER SQLERROR EXIT;
DEFINE vfrmdte = '&&1';
DEFINE vtodte = '&&2';
DEFINE vjobnum = '&&3';
DEFINE vorgid = '&&4';
select substr(we.wip_entity_name,1,10)||';'||
substr(msi.segment1,1,10)||';'||
rc.customer_number||';'||
rsu.location||';'||
substr(sl.cust_po_number,1,25)||';'|| --changed sh to sl
--sh.ordered_date||';'||
sl.creation_date||';'||----extra colmn added
sl.ordered_item||';'|| substr(sl.attribute8,1,30)||';'||
REPLACE(sl.ordered_item,'?',' ')||';'|| --substr(sl.attribute8,1,30)||';'||
sl.request_date||';'||
substr(dcgv.segment2,1,15)||';'||
substr(dgv.SEGMENT1,1,20)||';'||
sh.order_number||';'||
substr(dcgv.segment3,1,15)||';'||
substr(dcgv.segment4,1,25)||';'||
substr(dscv.segment2,1,20)||';'||
substr(dscv.segment3,1,10)||';'||
wdj.start_quantity||';'||
wdj.quantity_completed||';'||
sl.order_quantity_uom||';'||
wdj.completion_subinventory||';'||
wdj.completion_locator_id||';'||
wdj.primary_item_id||';'||
wdj.wip_entity_id||';'||
wdj.scheduled_start_date||';'||
wdj.status_type||';'||
substr(ml.meaning,1,30)||';'||
ml.lookup_code||';'||
md.demand_source_header_id||';'||
substr(md.demand_source_line,1,15)||';'||
mso.sales_order_id||';'||
substr(mso.segment1,1,15)||';'||
substr(mso.segment2,1,25)||';'||
substr(mso.segment3,1,20)||';'||
mic.category_set_id||';'||
mic2.category_set_id||';'||
dgv.category_id||';'||
dcgv.category_id||';'||
sh.header_id||';'||
sh.order_type_id||';'||
sl.line_number||';'||
sl.line_id||';'||
sl.inventory_item_id||';'||
substr(sl.attribute11,1,20)||';'||
msi.fixed_lot_multiplier||';'||
substr(dscv.segment1,1,20)||';'||
wdj.organization_id||';'||
rc.customer_id||';'||
rsu.site_use_id||';'||
sl.LAST_UPDATE_DATE------extra colmn added
from
apps.wip_entities we ,
apps.wip_discrete_jobs wdj ,
apps.mfg_lookups ml ,
--apps.wip_so_allocations wsa ,
apps.mtl_demand md, --inv.mtl_demand md ,
apps.mtl_sales_orders mso ,
apps.mtl_system_items_b msi,--inv.mtl_system_items_b msi ,
apps.mtl_item_categories mic ,
apps.mtl_item_categories mic2 ,
apps.mtl_item_categories mic3 ,
apps.mtl_category_sets mcs ,
apps.mtl_category_sets mcs2 ,
apps.mtl_category_sets mcs3 ,
apps.MTL_CATEGORIES_B dgv,--INV.MTL_CATEGORIES_B dgv ,
apps.MTL_CATEGORIES_B dcgv ,
apps.MTL_CATEGORIES_B dscv ,
apps.oe_order_headers_all sh, --so_headers_all sh ,
apps.oe_order_lines_all sl, --so_lines_all sl ,
apps.oe_transaction_types_tl sot, --so_order_types_all sot ,
apps.ra_customers rc ,
apps.ra_site_uses_all rsu
where
we.wip_entity_id = wdj.wip_entity_id
and wdj.firm_planned_flag in (1,2)
and wdj.status_type = ml.lookup_code
and ml.lookup_type = 'WIP_JOB_STATUS'
and md.supply_source_header_id = we.wip_entity_id
-- and sh.header_id = mso.sales_order_id
and mso.segment1 = sh.order_number
and mso.segment2 = sot.name
and sot.transaction_type_id = sh.order_type_id
and sh.sold_to_org_id = rc.customer_id
and sh.ship_to_org_id = rsu.site_use_id
--DUPLICATE and sh.order_type_id = sot.order_type_id
and sl.header_id = sh.header_id
and sl.line_id = md.demand_source_line
and sl.inventory_item_id = msi.inventory_item_id
and msi.organization_id = wdj.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and mic.category_set_id = mcs.category_set_id
and mcs.category_set_name = 'DWC Groups'
and dgv.category_id = mic.category_id
AND dgv.STRUCTURE_ID = 50109
and msi.inventory_item_id = mic2.inventory_item_id
and msi.organization_id = mic2.organization_id
and mic2.category_set_id = mcs2.category_set_id
and mcs2.category_set_name = 'DWC COLORS'
and dcgv.category_id = mic2.category_id
AND dcgv.STRUCTURE_ID = 50111
and msi.inventory_item_id = mic3.inventory_item_id
and msi.organization_id = mic3.organization_id
and mic3.category_set_id = mcs3.category_set_id
and mcs3.category_set_name = 'DWC Ship'
and dscv.category_id = mic3.category_id
AND dscv.STRUCTURE_ID = 50112
AND to_date(to_char(sl.request_date,'DD-MON-YYYY'),'DD-MON-YYYY') between
nvl(to_date(to_char('&vfrmdte','DD-MON-YYYY'),'DD-MON-YYYY'),sl.request_date)
and nvl(to_date(to_char('&vtodte' ,'DD-MON-YYYY'),'DD-MON-YYYY'),sl.request_date)
AND we.wip_entity_name = NVL('&vjobnum', we.wip_entity_name)
AND we.organization_id = NVL('&&4',we.organization_id)
AND wdj.status_type IN (1,3)
GROUP BY
substr(we.wip_entity_name,1,10)||';'||
substr(msi.segment1,1,10)||';'||
rc.customer_number||';'||
rsu.location||';'||
substr(sl.cust_po_number,1,25)||';'|| --changed from sh to sl
--sh.ordered_date||';'||
sl.creation_date||';'||
sl.ordered_item||';'|| substr(sl.attribute8,1,30)||';'||
REPLACE(sl.ordered_item,'?',' ')||';'|| --substr(sl.attribute8,1,30)||';'||
sl.request_date||';'||
substr(dcgv.segment2,1,15)||';'||
substr(dgv.SEGMENT1,1,20)||';'||
sh.order_number||';'||
substr(dcgv.segment3,1,15)||';'||
substr(dcgv.segment4,1,25)||';'||
substr(dscv.segment2,1,20)||';'||
substr(dscv.segment3,1,10)||';'||
wdj.start_quantity||';'||
wdj.quantity_completed||';'||
sl.order_quantity_uom||';'||
wdj.completion_subinventory||';'||
wdj.completion_locator_id||';'||
wdj.primary_item_id||';'||
wdj.wip_entity_id||';'||
wdj.scheduled_start_date||';'||
wdj.status_type||';'||
substr(ml.meaning,1,30)||';'||
ml.lookup_code||';'||
md.demand_source_header_id||';'||
substr(md.demand_source_line,1,15)||';'||
mso.sales_order_id||';'||
substr(mso.segment1,1,15)||';'||
substr(mso.segment2,1,25)||';'||
substr(mso.segment3,1,20)||';'||
mic.category_set_id||';'||
mic2.category_set_id||';'||
dgv.category_id||';'||
dcgv.category_id||';'||
sh.header_id||';'||
sh.order_type_id||';'||
sl.line_number||';'||
sl.line_id||';'||
sl.inventory_item_id||';'||
substr(sl.attribute11,1,20)||';'||
msi.fixed_lot_multiplier||';'||
substr(dscv.segment1,1,20)||';'||
wdj.organization_id||';'||
rc.customer_id||';'||
rsu.site_use_id||';'||
sl.LAST_UPDATE_DATE ----extra colmn added 

You are posting in the wrong forum. This is for Oracle Business Intelligence Applications, not Oracle 11i Applications aka Oralce EBusiness Suite

Related

Date is not Properly Sorting in Report Builder

AssalamoalikumI am running the below mention query--------------------------------------------------SELECT to_char(TO_date(H.INVOICE_DATE, 'DD-Mon-RR'), 'MON - RR') AS INVOICE_MONTH,       H.INVOICE_DATE AS invoice_Date,       H.GROUP_ID,       H.BUNIT_ID,       H.COMPANY_CODE,       C.NAME AS COMPANY_NAME,       H.BRANCH_CODE,       B.BRANCH_NAME,       H.WH_CODE,       WH.NAME WH_NAME,       H.INVOICE_NO,       H.EMP_CODE,       E.EMP_NAME,       D.INVOICE_SNO,       D.ITEM_CODE,       I.ITEM_NAME,       D.QUANTITY,       D.RATE,       D.AMOUNT,       D.REMARKS  FROM INVOICE_HEADER            H,       INVOICE_DETAILS           D,       COMPANY                   C,       BRANCH                    B,       WAREHOUSES                WH,       EMPLOYEE                  E,       ITEMS                     I,       WAREHOUSE_ITEM_ALLOCATION W WHERE H.INVOICE_NO = D.INVOICE_NO   AND H.GROUP_ID = D.GROUP_ID   AND H.BUNIT_ID = D.BUNIT_ID   AND H.BRANCH_CODE = B.BRANCH_CODE   AND H.GROUP_ID = B.GROUP_ID   AND H.BUNIT_ID = B.BUNIT_ID   AND H.COMPANY_CODE = B.COMPANY_CODE   AND H.GROUP_ID = WH.GROUP_ID   AND H.BUNIT_ID = WH.BUNIT_ID   AND H.WH_CODE = WH.WH_CODE   AND H.COMPANY_CODE = WH.COMPANY_CODE   AND H.BRANCH_CODE = WH.BRANCH_CODE   AND H.COMPANY_CODE = C.COMPANY_CODE   AND H.GROUP_ID = C.GROUP_ID   AND H.BUNIT_ID = C.BUNIT_ID   AND H.EMP_CODE = E.EMP_CODE   AND H.GROUP_ID = E.GROUP_ID   AND H.BUNIT_ID = E.BUNIT_ID   AND D.ITEM_CODE = I.ITEM_CODE   AND H.GROUP_ID = I.GROUP_ID   AND H.BUNIT_ID = I.BUNIT_ID   AND D.ITEM_CODE = W.ITEM_CODE   AND D.GROUP_ID = W.GROUP_ID   AND D.BUNIT_ID = W.BUNIT_ID   AND D.COMPANY_CODE = W.COMPANY_CODE   AND D.WH_CODE = W.WH_CODE   AND D.BRANCH_CODE = W.BRANCH_CODE   AND I.ITEM_CODE = W.ITEM_CODE   AND I.GROUP_ID = W.GROUP_ID   AND I.BUNIT_ID = W.BRANCH_CODE   AND H.CANCEL_YN = 'N'   AND D.CANCEL_YN = 'N'   AND C.CANCEL_YN = 'N'   AND B.CANCEL_YN = 'N'   AND WH.CANCEL_YN = 'N'   AND E.CANCEL_YN = 'N'   AND I.CANCEL_YN = 'N'/*   AND H.GROUP_ID LIKE NVL(:P_GROUP_ID, h.group_id)   AND H.BUNIT_ID LIKE NVL(:P_BUNIT_ID, h.bunit_id)   AND H.COMPANY_CODE LIKE NVL(:P_COMPANY_CODE, h.company_code)   AND H.BRANCH_CODE LIKE NVL(:P_BRANCH_CODE, h.branch_code)   AND H.WH_CODE LIKE NVL(:P_WH_CODE, h.wh_code)   AND H.INVOICE_NO LIKE NVL(:p_invoice, h.invoice_no)   AND h.emp_code LIKE NVL(:p_emp_code, h.emp_code)*/--AND h.invoice_date BETWEEN NVL(to_char(:p_start_date, 'fmMON - RR'), h.invoice_date) AND NVL(to_char(:p_end_date, 'fmMON - RR'), h.invoice_date) ORDER BY TO_date(to_char(H.INVOICE_DATE, 'Mon - RR'), 'Mon - RR')----------------------------------------------------------------------------which give me the result which is in attach file. I have the Problem in "INVOICE_MONTH" Field here it is giving correct result (order by) but in Oracle Report Builder 11.1.2.2.0 it is not functioning correctly as mention the attach file name "Report Builder Query"
Dear, In order to do the ORDER properly, it will be better to convert the date to NUMBER than STRING. Because in string, Aug will come first than Jan. Try converting the date to RRRRMMDD format (Year in 4 digits first, followed by month in 2 digits and if required, date in 2 digits. Hope this helps. Manu.
BrotherI want format like MON - RR, 'JAN - 15', I try to follow your advice.
Dear, You have to use new field for the sorting. Show the date as MON-RR and sort using RRRRMM. Hope this helps. Manu.
Dear Manu,Thanks for your instant response and nice coordination. The problem has solved through different ways, find the attach query  --------------------------------SELECT to_date(TO_CHAR(H.INVOICE_DATE, 'Mon - RR'), 'FMMON - RR') AS INVOICE_MONTH,       H.INVOICE_DATE AS invoice_Date,       H.GROUP_ID,       H.BUNIT_ID,       H.COMPANY_CODE,       C.NAME AS COMPANY_NAME,       H.BRANCH_CODE,       B.BRANCH_NAME,       H.WH_CODE,       WH.NAME WH_NAME,       H.INVOICE_NO,       H.EMP_CODE,       E.EMP_NAME,       D.INVOICE_SNO,       D.ITEM_CODE,       I.ITEM_NAME,       D.QUANTITY,       D.RATE,       D.AMOUNT,       D.REMARKS  FROM INVOICE_HEADER            H,       INVOICE_DETAILS           D,       COMPANY                   C,       BRANCH                    B,       WAREHOUSES                WH,       EMPLOYEE                  E,       ITEMS                     I,       WAREHOUSE_ITEM_ALLOCATION W WHERE H.INVOICE_NO = D.INVOICE_NO   AND H.GROUP_ID = D.GROUP_ID   AND H.BUNIT_ID = D.BUNIT_ID   AND H.BRANCH_CODE = B.BRANCH_CODE   AND H.GROUP_ID = B.GROUP_ID   AND H.BUNIT_ID = B.BUNIT_ID   AND H.COMPANY_CODE = B.COMPANY_CODE   AND H.GROUP_ID = WH.GROUP_ID   AND H.BUNIT_ID = WH.BUNIT_ID   AND H.WH_CODE = WH.WH_CODE   AND H.COMPANY_CODE = WH.COMPANY_CODE   AND H.BRANCH_CODE = WH.BRANCH_CODE   AND H.COMPANY_CODE = C.COMPANY_CODE   AND H.GROUP_ID = C.GROUP_ID   AND H.BUNIT_ID = C.BUNIT_ID   AND H.EMP_CODE = E.EMP_CODE   AND H.GROUP_ID = E.GROUP_ID   AND H.BUNIT_ID = E.BUNIT_ID   AND D.ITEM_CODE = I.ITEM_CODE   AND H.GROUP_ID = I.GROUP_ID   AND H.BUNIT_ID = I.BUNIT_ID   AND D.ITEM_CODE = W.ITEM_CODE   AND D.GROUP_ID = W.GROUP_ID   AND D.BUNIT_ID = W.BUNIT_ID   AND D.COMPANY_CODE = W.COMPANY_CODE   AND D.WH_CODE = W.WH_CODE   AND D.BRANCH_CODE = W.BRANCH_CODE   AND I.ITEM_CODE = W.ITEM_CODE   AND I.GROUP_ID = W.GROUP_ID   AND I.BUNIT_ID = W.BRANCH_CODE   AND H.CANCEL_YN = 'N'   AND D.CANCEL_YN = 'N'   AND C.CANCEL_YN = 'N'   AND B.CANCEL_YN = 'N'   AND WH.CANCEL_YN = 'N'   AND E.CANCEL_YN = 'N'   AND I.CANCEL_YN = 'N' /*  AND H.GROUP_ID LIKE NVL(:P_GROUP_ID, h.group_id)   AND H.BUNIT_ID LIKE NVL(:P_BUNIT_ID, h.bunit_id)   AND H.COMPANY_CODE LIKE NVL(:P_COMPANY_CODE, h.company_code)   AND H.BRANCH_CODE LIKE NVL(:P_BRANCH_CODE, h.branch_code)   AND H.WH_CODE LIKE NVL(:P_WH_CODE, h.wh_code)   AND H.INVOICE_NO LIKE NVL(:p_invoice, h.invoice_no)   AND h.emp_code LIKE NVL(:p_emp_code, h.emp_code)*/--AND h.invoice_date BETWEEN NVL(to_char(:p_start_date, 'fmMON - RR'), h.invoice_date) AND NVL(to_char(:p_end_date, 'fmMON - RR'), h.invoice_date) ORDER BY TO_date(to_char(H.INVOICE_DATE, 'Mon - RR'), 'Mon - RR')--------------------------------and in the Report Builder i use the "Formula Column", The query for it mention below: --------------------------------function month_yearFormula return char isv_date VARCHAR2(20);begin  v_date := REPLACE(:INVOICE_MONTH1, '01-', '');  return (v_Date);EXCEPTIONWHEN OTHERS THENRETURN (SQLERRM);end;--------------------------------Finally the output as I desire  Regards,Omar Baig
Dear, Glad to know the issue is solved. Please mark the appropriate helpful and correct reply and close the thread! Manu.
 ORDER BY TO_date(to_char(H.INVOICE_DATE, 'Mon - RR'), 'Mon - RR')There is no need to convert a date to a string and back, just do ORDER BY H.INVOICE_DATE
Dear Cormaco,This is I am already doing but it is not reflecting in report Builder, if you see the above mention query.

ODP.NET  ORA-01002

We have some pl/sql code that returns a refcursor. I am able to call the proc via SQL*PLUS but when I try and call it in my c# app i get and ora-01002. Any ideas? 
Any ideas?Possible to post the relevant code? Kind of difficult to make any suggestions without any detail.
- Mark 
The proc was returning rownum as part of the result set. I removed rownum and the proc worked fine. Not sure why. Here is the orginal sql code.
BEGIN
/* all columns were entered */
IF (length(IN_INTERVENTION_ID) > 0)
THEN
OPEN OUT_POPULATION FOR
WITH CLAIMS as (
select distinct dw.mem_sk, dw.mem_id, dw.first_nam,dw.lst_nam, dw.middle_initial, dw.birth_dt,
nvl(intexc.notes,modexc.notes) as notes,
nvl(intexc.is_excluded,modexc.is_excluded) as is_excluded,
nvl(intexc.exclusion_type,modexc.exclusion_type) as exclusion_type,
nvl(intexc.exclusion_start_date,modexc.exclusion_start_date) as exclusion_start_date,
nvl(intexc.exclusion_end_date,modexc.exclusion_end_date) as exclusion_end_date,
NULL as rank
from dw_nav.dur_claim_intervention_stg dw,
dur_nav.intervention_app intervention_app,
dur_nav.cust_intervention_period_app cust_intervention_period_app,
dur_nav.cust_intervention_carrier_app cust_intervention_carrier_app,
dur_nav.intervention_drug_app intervention_drug_app,
(select intervention_member_app.member_sk,
intervention_member_app.is_excluded,
decode(intervention_member_app.is_excluded,'1','Intervention','Included') as exclusion_type,
decode(intervention_member_app.is_excluded,'0',null,pre_intervention_start_date) as exclusion_start_date,
decode(intervention_member_app.is_excluded,'0',null,post_intervention_end_date) as exclusion_end_date,
intervention_member_app.notes
from dur_nav.intervention_member_app intervention_member_app, dur_nav.intervention_app intervention_app
where intervention_app.intervention_id = intervention_member_app.intervention_id
and intervention_member_app.intervention_id = IN_INTERVENTION_ID) intexc,
(select member_module_exclusion_app.member_sk,
'1' as is_excluded,
'Module' as exclusion_type,
start_date as exclusion_start_date,
end_date as exclusion_end_date,
member_module_exclusion_app.notes
from dur_nav.member_module_exclusion_app member_module_exclusion_app, dur_nav.intervention_app intervention_app
where intervention_app.module_id = member_module_exclusion_app.module_id
and intervention_app.intervention_id = IN_INTERVENTION_ID) modexc
where intervention_app.cust_intervention_period_id = cust_intervention_period_app.cust_intervention_period_id
and DECIMAL_QTY/DAYS_SUP >= 2
and DAYS_SUP >= 28
and (dw.prior_auth_thru_dt is null or dw.prior_auth_thru_dt < intervention_app.pre_intervention_end_date)
and cust_intervention_period_app.customer = dw.customer
AND (dw.THRU_DT IS NULL OR dw.THRU_DT >= sysdate)
and intervention_app.cust_intervention_period_id = cust_intervention_carrier_app.cust_intervention_period_id
and cust_intervention_carrier_app.carrier = dw.carrier
and dw.svc_dt between intervention_app.pre_intervention_start_date and intervention_app.pre_intervention_end_date
and intervention_drug_app.intervention_id = intervention_app.intervention_id
and(dw.submit_prdct_id = intervention_drug_app.ndc or dw.drug_dea_cd = intervention_drug_app.dea_drug_class_code or dw.genr_prdct_id like intervention_drug_app.partial_gpi||decode(intervention_drug_app.partial_gpi,null,null,'%'))
and intervention_drug_app.is_included = 1
and intervention_app.intervention_id = IN_INTERVENTION_ID
and dw.mem_sk = intexc.member_sk (+)
and dw.mem_sk = modexc.member_sk (+)
and 2 > (select count (distinct ima.intervention_id)
from dur_nav.intervention_member_app ima, dur_nav.module_app ma,
dur_nav.intervention_app ia, dur_nav.intervention_drug_app ida
where ima.member_sk = dw.mem_sk
and ima.is_excluded = 0
and ima.intervention_id = ia.intervention_id
and intervention_app.module_id = ia.module_id
and ia.intervention_id = ida.intervention_id
and(dw.submit_prdct_id = ida.ndc or dw.drug_dea_cd = ida.dea_drug_class_code or dw.genr_prdct_id like ida.partial_gpi||decode(intervention_drug_app.partial_gpi,null,null,'%'))
and ida.is_included = 1)
and not exists (select 'x' from dur_nav.intervention_drug_app intervention_drug_app
where intervention_drug_app.intervention_id = IN_INTERVENTION_ID
and(dw.submit_prdct_id = intervention_drug_app.ndc or dw.drug_dea_cd = intervention_drug_app.dea_drug_class_code or dw.genr_prdct_id like intervention_drug_app.partial_gpi||decode(intervention_drug_app.partial_gpi,null,null,'%'))
and intervention_drug_app.is_included = 0)
),
selcount as (select count(1)*max(NVL(population_selection_percent,100))/100 memcnt
from CLAIMS,dur_nav.intervention_app intervention_app
where intervention_app.intervention_id = IN_INTERVENTION_ID)
select rownum,mem_sk,mem_id,first_nam,lst_nam,middle_initial,birth_dt,rank,
notes,is_excluded,exclusion_type,exclusion_start_date,exclusion_end_date
from CLAIMS,selcount
where rownum <= memcnt;
END IF;

SQL not returning data in sql developer but does when ran in an sql session

Hi Guys
Wonder if anyone can help. I am new to sql developer.
I am running in a peice of sql but am getting no results back. when i run it in an sql session on our server it works correctly. I am just wondering if i am doing somthing wrong. I am running the sql to get data from Oracle FMS and need to set the following data first in order to get the data from the tables.
begin
fnd_client_info.set_org_context(0);
end;
I then run the code
SELECT DISTINCT pti.attribute_value
,ph.segment1
,pti2.attribute_value
,gcc.segment3
,phc.hazard_class
,REPLACE(REPLACE(SUBSTR(pl.item_description, 1, 47),CHR(13),' '),CHR(10),' ')
,pl.unit_price
,DECODE(aid.invoice_id, aid.invoice_id, (SELECT ai.invoice_num
FROM ap_invoices ai
WHERE ai.INVOICE_ID = aid.invoice_id))
,DECODE(aid.batch_id, aid.batch_id, (SELECT ab.batch_name FROM ap_batches ab WHERE ab.batch_id = aid.batch_id))
,po.quantity_ordered
,po.quantity_delivered
,aid.quantity_invoiced
,DECODE ((SELECT DISTINCT 1
FROM ap_holds ah
WHERE ah.status_flag = 'S'
AND ah.invoice_id = aid.invoice_id
AND ah.line_location_id = po.line_location_id
AND po.po_line_id = pl.po_line_id
AND pl.line_num = aid.distribution_line_number ), '1', 'On Hold', NULL) hold
,(SELECT SUM(aid3.amount) FROM ap_invoice_distributions aid3 , ap_invoices ai3
WHERE aid3.po_distribution_id = aid.po_distribution_id
AND aid3.invoice_id = ai3.invoice_id
AND ai3.invoice_type_lookup_code = 'CREDIT') credit
FROM por_template_info pti
,por_template_info pti2
,por_template_attributes_tl pta
,por_template_attributes_tl pta2
,po_req_distributions pr
,po_distributions po
,po_headers ph
,po_lines pl
,po_hazard_classes phc
,gl_code_combinations gcc
,ap_invoice_distributions aid
,icx_category_lov icl
WHERE pti.attribute_code = pta.attribute_code
AND UPPER(pta.attribute_name) IN ('IT SERVICES RFS')
AND pti2.attribute_code = pta2.attribute_code
AND UPPER(pta2.attribute_name) IN ('IT CHARGE CENTRE INFO')
AND EXISTS (SELECT pti3.attribute_value
FROM por_template_info pti3
,por_template_attributes_tl pta3
WHERE UPPER(pti3.attribute_value) = 'YES'
AND pti3.requisition_line_id = pti.requisition_line_id
AND pti3.attribute_code = pta3.attribute_code
AND UPPER(pta3.attribute_name) IN ('LEASED ITEM'))
AND ph.segment1 NOT IN ('3000516', '3000528')
AND pti.requisition_line_id = pti2.requisition_line_id
AND pti.requisition_line_id = pr.requisition_line_id
AND pr.distribution_id = po.req_distribution_id
AND po.po_header_id = ph.po_header_id
AND ph.po_header_id = pl.po_header_id
AND ph.cancel_flag IS NULL
AND pl.cancel_flag IS NULL
AND pl.hazard_class_id = phc.hazard_class_id
AND po.code_combination_id = gcc.code_combination_id
AND pl.category_id = icl.category_id
AND po.po_distribution_id = aid.po_distribution_id (+)
AND po.po_line_id = pl.po_line_id
AND (aid.po_distribution_id IS NULL
OR
(EXISTS (SELECT DISTINCT 1
FROM po_headers ph2
,ap_invoice_distributions aid2
,ap_invoices ai2
,po_distributions po2
,po_lines pl2
WHERE ph2.segment1 = ph.segment1
AND ph2.po_header_id = po2.po_header_id
AND po2.po_distribution_id = aid2.po_distribution_id
AND aid2.invoice_id = ai2.invoice_id
AND ph2.cancel_flag IS NULL
AND pl2.cancel_flag IS NULL
AND ph2.po_header_id = pl2.po_header_id
AND pl2.po_line_id = po2.po_line_id
AND pl2.unit_price != 0
AND ( ai2.payment_status_flag != 'Y'
OR po2.quantity_ordered != po2.quantity_billed) ) )
OR
(EXISTS (SELECT 1
FROM po_headers ph3
,po_distributions po3
,po_lines pl3
WHERE ph3.po_header_id = po3.po_header_id
AND ph3.po_header_id = pl3.po_header_id
AND pl3.po_line_id = po3.po_line_id
AND ph3.segment1 = ph.segment1
AND ph3.cancel_flag IS NULL
AND pl3.cancel_flag IS NULL
AND po3.quantity_billed = 0
AND pl3.unit_price != 0 ) ) )
AND aid.reversal_flag (+) IS NULL
AND pl.unit_price != 0
AND (UPPER(icl.concatenated_segments) = ('INFORMATION TECHNOLOGY.DESKTOP HARDWARE.SLC')
OR UPPER(icl.concatenated_segments) = ('INFORMATION TECHNOLOGY.IT PERIPHERALS LEASE.SLC'))
ORDER BY ph.segment1, pti.attribute_value;
I get no rows back in SQL developer but 41 when i run the same queries from an sql session.
Can anyone help?
Regards
Ruth 
Further information.....
I have worked it down to too tables po_hazard_classes and icx_category_lov which are not returning any data in sql developer but do when i run them in an sql session. I am connecting to the Database as the same user (Apps) in both cases. Is there anyone who has used sql developer with Oracle Financials who might know why this is?
Regards
Ruth 
This is almost certainly down to your language settings in SQL Developer. Many of the oracle financials tables are views based on language settings. Search the forum for subjects similar to yours. This has been answered many times 
Here is a link to the most recent example
SQL Developer not providing query results. 
Hi Jim
Thanks for the link.
My language settings are GB and they are set to united knigdom in Tools->Preferences->database ->NLS Parameters
so i dont think that its the language setting
Ruth 
what does "select userenv('LANG') from dual;" return in SQLDeveloper and in SQL Plus? 
Hi Jim
In sql developer i get GB
In sql plus i get US
I changed the nls language prefs to American and it worked
Many thanks
Regards
Ruth

DAC Error

Hello Friends,
I am using BI Apps 7.9.6.1 and the module is HR-Workforce from EBS Vision 12.1.1
This is the query for Work flow SDE_ORA_PersistedStage_WorkforceEvent_SupervisorStatus
The only thing is that when I run this workflow I am getting the error not a group by function, how do I resolve this issue
SELECT
PERSON_ID AS PERSON_ID
+,EVENT_DATE AS EFFECTIVE_START_DATE+
+,SUPERVISOR_FLAG AS SUPERVISOR_FLAG+
+,SUPERVISOR_PRV_FLAG AS SUPERVISOR_PRV_FLAG+
+,PERSON_HIRE_EVENT_IND AS PERSON_HIRE_EVENT_IND+
+,DATASOURCE_NUM_ID AS DATASOURCE_NUM_ID+
FROM
+(+
SELECT
CHGS.PERSON_ID
+,CHGS.EVENT_DATE+
+,CASE WHEN (SELECT 'Y'+
FROM W_PARAM_G
WHERE EXISTS
+(SELECT NULL+
FROM W_ORA_WEVT_ASG_PS  SUP
WHERE CHGS.PERSON_ID = SUP.SUPERVISOR_ID
AND CHGS.DATASOURCE_NUM_ID = SUP.DATASOURCE_NUM_ID
AND SUP.TERM_EVENT_IND SUP.ASG_END_EVENT_IND = 0+
AND CHGS.EVENT_DATE BETWEEN SUP.EFFECTIVE_START_DATE
AND SUP.EFFECTIVE_END_DATE)) IS  NULL THEN 'N' ELSE 'Y' END    SUPERVISOR_FLAG
+,CASE WHEN (SELECT 'Y'+
FROM W_PARAM_G
WHERE EXISTS
+(SELECT NULL+
FROM W_ORA_WEVT_ASG_PS  SUP
WHERE CHGS.PERSON_ID = SUP.SUPERVISOR_ID
AND CHGS.DATASOURCE_NUM_ID = SUP.DATASOURCE_NUM_ID
AND SUP.TERM_EVENT_IND SUP.ASG_END_EVENT_IND = 0+
AND CHGS.EVENT_DATE_PRV BETWEEN SUP.EFFECTIVE_START_DATE
AND SUP.EFFECTIVE_END_DATE)) IS NULL THEN 'N' ELSE 'Y' END SUPERVISOR_PRV_FLAG
+,CHGS.PERSON_HIRE_EVENT_IND+
+,CHGS.DATASOURCE_NUM_ID+
FROM
+(SELECT+
CASE WHEN EVT.HIRE_EVENT_IND = 1 AND REF.TYP = 'IN_OR_PSN'
THEN EVT.PERSON_ID
WHEN (EVT.SUP_CHANGE_IND = 1 OR EVT.TERM_EVENT_IND = 1 OR EVT.ASG_END_EVENT_IND = 1) AND
REF.TYP = 'OUT_OR_SUP'
THEN EVT.SUPERVISOR_PRV_ID
ELSE EVT.SUPERVISOR_ID
END                             PERSON_ID
+,EVT.EFFECTIVE_START_DATE EVENT_DATE+
+,EVT.PREV_EFF_END_DATE EVENT_DATE_PRV+
+,MAX(CASE WHEN EVT.HIRE_EVENT_IND = 1 AND REF.TYP = 'IN_OR_PSN' THEN 1 ELSE 0 END)+
PERSON_HIRE_EVENT_IND
+,EVT.DATASOURCE_NUM_ID+
FROM
W_ORA_WEVT_ASG_PS  EVT
+,(SELECT 'IN_OR_PSN' TYP FROM W_PARAM_G UNION ALL SELECT 'OUT_OR_SUP' TYP FROM W_PARAM_G) REF+
WHERE (EVT.HIRE_EVENT_IND      = 1 OR
EVT.ASG_START_EVENT_IND = 1 OR
EVT.SUP_CHANGE_IND      = 1 OR
EVT.TERM_EVENT_IND      = 1 OR
EVT.ASG_END_EVENT_IND   = 1 OR
EVT.FIRST_RECORD_IND    = 1)
AND ((EVT.TERM_EVENT_IND = 0 AND EVT.ASG_END_EVENT_IND = 0)
OR REF.TYP = 'OUT_OR_SUP')
GROUP BY
CASE WHEN EVT.HIRE_EVENT_IND = 1 AND REF.TYP = 'IN_OR_PSN'
THEN EVT.PERSON_ID
WHEN (EVT.SUP_CHANGE_IND = 1 OR EVT.TERM_EVENT_IND = 1 OR EVT.ASG_END_EVENT_IND = 1) AND
REF.TYP = 'OUT_OR_SUP'
THEN EVT.SUPERVISOR_PRV_ID
ELSE EVT.SUPERVISOR_ID
END
+,EVT.DATASOURCE_NUM_ID+
+,EVT.EFFECTIVE_START_DATE+
+,EVT.PREV_EFF_END_DATE+
+) CHGS+
WHERE CHGS.PERSON_ID IS NOT NULL
+) TAB+
WHERE (TAB.SUPERVISOR_FLAG != TAB.SUPERVISOR_PRV_FLAG
OR TAB.PERSON_HIRE_EVENT_IND = 1)
ORDER BY  PERSON_ID , DATASOURCE_NUM_ID,EVENT_DATE desc 
Hi
I ran into same situation. There is no problem with query. it depends upon the target db if you(or DBA) have enabled complex view merging. Follow as below to run this job.
Or if you want to test in sqlplus by running the step 5 and run your query,you should get results.
1. Open Informatica PowerCenter WorkFlow Manager
2 In the top menu, go to Connections > Relational
3. Select the Oracle - DataWarehouse connection (Type - Object) and press the "Edit" button
4. In the "Connection Object Definition" dialog box, go to the "Connection Environment SQL" attribute and click the down arrow in the Value field
5. In the "SQL Editor - DataWarehouse (Connection)" dialog box, enter the following in the "SQL:" field
alter session set "_complex_view_merging" = false;
vi. Click OK, OK, Close
Let me know how it goes..
Thanks
Vijay 
Vijay,
Thanks that worked like a charm.

ASCP BOM Explosion SQL

Hi All I need SQL that will find all the components with certain planner if I pass a End Item.The component can be at any level in the BOM.This is something similar to 'Indented BOM' on Oracle side. But I want to run this on ASCP side.I am ok even if someone can provide SQL for 'Item WhereUsed' on ASCP side.Thanks
Hi, I am not a developer but just attempted to create this query. It appears to be working fine, but please fine tune it if it has issues: select level, operation_seq_num, bom.assembly_item_id, msi.item_name Assembly_name, bic.inventory_item_id, msic.item_name component_name, BIC.usage_quantity, bic.effectivity_date, bic.disable_datefrom msc_boms BOM , msc_bom_components BIC,msc_system_items msi,msc_system_items msicwhere 1=1and bom.bill_sequence_id = bic.bill_sequence_idand bom.plan_id = bic.plan_idand bom.organization_id = bic.organization_idand bic.inventory_item_id = msic.inventory_item_idand bic.organization_id = msic.organization_idand bic.plan_id = msic.plan_idand bom.assembly_item_id = msi.inventory_item_idand bom.organization_id = msi.organization_idand bom.plan_id = msi.plan_idand msi.ORGANIZATION_ID = 207 and msi.plan_id = -1Connect by prior bic.inventory_item_id  = bom.assembly_item_id START WITH msi.item_name  =  'MB_PF' and bom.ORGANIZATION_ID = 207 and bom.plan_id = -1ORDER BY bom.plan_id, level, operation_seq_num, msi.item_name Hope that helps! Thanks and Regards,Mohan Balaji

Categories

Resources