Monday 30 March 2020

How to change record group query by using custom.pll

 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