IF event_name = 'WHEN-NEW-FORM-INSTANCE' AND form_name = 'OKSAUDET'
THEN
v_sql :=
'select CII.instance_number
, CII.SERIAL_NUMBER Serial_number
, B.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS
, T.DESCRIPTION DESCRIPTION
, CII.external_reference external_reference
, CII.Instance_id
, ''OKX_CUSTPROD'' Object_Code
, ''#'' Id2
, okx.unit_of_measure_tl unit_of_measure
, CII.Quantity
, CII.Inventory_Item_Id
, CIOA.operating_unit_id
, TO_NUMBER(NULL) unit_selling_price
, DECODE (GREATEST(SYSDATE , NVL(B.START_DATE_ACTIVE, SYSDATE )),LEAST(SYSDATE ,
NVL(B.END_DATE_ACTIVE, SYSDATE )), T.DESCRIPTION||'';''||CII.SERIAL_NUMBER||'';''||CII.INSTANCE_NUMBER, NULL) item_description
, CII.Unit_Of_Measure uom_code
FROM MTL_SYSTEM_ITEMS_B_KFV B
, MTL_SYSTEM_ITEMS_TL T
, CSI_ITEM_INSTANCES CII
, CSI_I_ORG_ASSIGNMENTS CIOA
, okx_units_of_measure_v okx
WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
and B.ORGANIZATION_ID = T.ORGANIZATION_ID
and T.LANGUAGE = userenv(''LANG'')
and B.INVENTORY_ITEM_ID = CII.inventory_item_id
and B.serviceable_product_flag = ''Y''
and B.ORGANIZATION_ID = SYS_CONTEXT(''OKC_CONTEXT'',''ORGANIZATION_ID'')
AND CIOA.instance_id(+) = CII.instance_id
AND CIOA.relationship_type_code (+) = ''SOLD_FROM''
AND cii.Unit_Of_Measure = okx.uom_code
AND TRUNC(NVL(okx.disable_date,sysdate)) >= TRUNC(sysdate)
order by cii.instance_number, cii.serial_number,B.CONCATENATED_SEGMENTS,T.DESCRIPTION';
rg_id := FIND_GROUP ('REFERENCE_NUMBER_DESC');
IF NOT ID_NULL (rg_id)
THEN
rgp_id := POPULATE_GROUP_WITH_QUERY (rg_id, v_sql);
pg_id := POPULATE_GROUP (rg_id);
END IF;
END IF;
No comments:
Post a Comment