IndahnyaBerbagi

PROCEDURE POPULATE_EMP IS
L_rg_name VARCHAR2(40);
L_rg_id RecordGroup;
L_errcode NUMBER;
L_Query VARCHAR2(2000);
BEGIN
/***
** Fetch Batch year , batch number from als_batch_info table
** and pass it as record group to show the LOV on same items
***/
If :System.Cursor_Item = 'EMPID' Then
L_rg_name := 'EMPNO_REC_GRP';
L_Query := ' SELECT EMPID,EMPNAME'||
' FROM EMP '||
' Where EMPID LIKE Nvl('||''''||:EMPID||'%'||''''||',EMPID)'||
' AND EMPNAME LIKE Nvl('||''''||:EMPNAME||'%'||''''||',EMPNAME)'||
' Order By 1 desc ,2 desc';

L_Query := ' select msi.INVENTORY_ITEM_ID,msi.SEGMENT1 ITEM_NAME,msi.ATTRIBUTE1 TYPE,msi.ATTRIBUTE4 KODE_MODEL,msi.ATTRIBUTE2 KODE_WARNA,msi.ATTRIBUTE3 WARNA'||
' from mtl_system_items msi '||
' where msi.ATTRIBUTE_CATEGORY = ''H1-ITEM'''||
' and msi.INVENTORY_ITEM_STATUS_CODE =''Active'''||
' and msi.ORGANIZATION_ID = '''||:PARAMETER.ORGANIZATION_ID||''''
' and exists ( select ''x'''||
' FROM XXH1_P2P_RANK_HEADERS RH, '||
' XXH1_P2P_RANK_LINES RL '||
' WHERE RL.XXH1PRH_ID = RH.XXH1PRH_ID '||
' AND NVL(RH.XXH1PRH_FLAG,''N'') = ''Y'''||
' AND RL.XXH1PRL_ITEM = msi.SEGMENT1 '||
' AND RL.XXH1PRL_RANK_CODE = ''C'')';

L_rg_id := Find_Group( L_rg_name );

/*Populate the record group thru query*/
L_errcode := Populate_Group_With_Query( L_rg_id, L_Query );
If L_errcode <> 0 Then
Message(SQLERRM);
End If;
ElsIf :System.Cursor_Item = 'EMPNAME' Then
L_rg_name := 'BATCHNO_REC_GRP';
L_Query := ' SELECT EMPID,EMPNAME'||
' FROM EMP '||
' Where EMPID LIKE Nvl('||''''||:EMPID||'%'||''''||',EMPID)'||
' AND EMPNAME LIKE Nvl('||''''||:EMPNAME||'%'||''''||',EMPNAME)'||
' Order By 1 desc ,2 desc';
L_rg_id := Find_Group( L_rg_name );
L_rg_id := Find_Group( L_rg_name );

/*Populate the record group thru query*/
L_errcode := Populate_Group_With_Query( L_rg_id, L_Query );
If L_errcode <> 0 Then
Message(SQLERRM);
End If; End If;
Exception
When Others Then
Template.Show_Error('T');
END;