Script to update pa deliverable action (pa_project_pub.update_deliverable_action)

DECLARE

l_api_version_number NUMBER := 1.0;
l_init_msg_list VARCHAR2 (1) := 'F';
l_commit VARCHAR2 (1) := 'F';
l_return_status VARCHAR2 (1);
l_action_out                pa_project_pub.action_out_tbl_type;
l_msg_count                 NUMBER := 0;
        l_msg_data                  VARCHAR2 (2000);
        l_msg_dummy                 VARCHAR2 (2000);

BEGIN

fnd_file.put_line (fnd_file.LOG,'nuan_deliverable_action_upd start');

pa_project_pub.update_deliverable_action (
  p_api_version => l_api_version_number
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, x_return_status => l_return_status--l_return_status
, p_action_name => 'CTX Go Live'--action_rec.action_name  pa_dlvr_actions_v.element_name
, p_action_id => 4964902--action_rec.proj_element_id pa_dlvr_actions_v.proj_element_id
, p_pm_action_reference => 'PGL_CONVERSION'--action_rec.pm_source_reference--pa_deliverables_v.pm_source_reference
, p_action_owner_id => 19132--action_rec.manager_person_id pa_dlvr_actions_v.manager_person_id
, p_due_date => '26-MAR-2020'--l_due_date --pass due date
, p_completion_date => '26-MAR-2020' --pass completion_date
, p_deliverable_id => 4964900--action_rec.deliverable_id --pa_deliverables_v.proj_element_id
, p_project_id => 331512--action_rec.project_id --pa_dlvr_actions_v.project_id
, x_action_out => l_action_out--l_action_out
, p_pm_source_code => NULL--l_pm_product_code
, x_msg_count => l_msg_count--l_msg_count
, x_msg_data => l_msg_data--l_msg_data
);

COMMIT;

fnd_file.put_line (fnd_file.LOG,'pa_project_pub.update_deliverable_action return status:'||l_return_status);

IF l_return_status <> 'S'
THEN
--log_msg('deliverable_action api is in error');
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i,fnd_api.g_false,l_msg_data,l_msg_dummy );
--p_error_message :=v_msg_data;
   -- p_return_status :='E';
   -- log_msg(v_msg_data);
END LOOP;
fnd_file.put_line (fnd_file.LOG,'API Error for action :'||'Action_name :'||l_msg_data);
ELSE
fnd_file.put_line (fnd_file.LOG,'API is successfully updated for action :'||'CTX Go Live');
END IF;

fnd_file.put_line (fnd_file.LOG,'nuan_deliverable_action_upd start');

END;

Script to update pa deliverable (pa_project_pub.update_deliverable)

Declare

l_api_version_number          NUMBER := 1.0;
l_init_msg_list VARCHAR2 (1) := 'F';
l_commit VARCHAR2 (1) := 'F';
l_debug_mode VARCHAR2 (1) := 'N';
l_return_status                VARCHAR2 (1);
l_completion_date  DATE;
l_deliverable_id              NUMBER;
l_proj_source_reference        pa_proj_elements.pm_source_reference%TYPE  := 'PGL_CONVERSION'; --pa_deliverables_v.pm_source_reference
    l_action_in                    pa_project_pub.action_in_tbl_type;
    l_action_out                  pa_project_pub.action_out_tbl_type;
l_pm_source_code              pa_proj_elements.pm_source_code%TYPE := NULL;
l_msg_count                    NUMBER := 0;
l_msg_data                    VARCHAR2 (4000);
v_msg_data          VARCHAR2(4000);
l_pgld_error_count            NUMBER := 0;
    l_msg_dummy                    VARCHAR2 (4000);
g_enter                        VARCHAR2 (10) := CHR (10);


BEGIN

fnd_file.put_line (fnd_file.LOG,'nuan_deliverable_upd start');

l_deliverable_id := 4964900;--5103921; pa_deliverables_v.proj_element_id

        fnd_global.apps_initialize ( 120865--p_user_id
                                , 57461--l_resp_id
                                , 275--- l_appln_id
                                 );


fnd_file.put_line (fnd_file.LOG,'l_deliverable_id:'||l_deliverable_id);
fnd_file.put_line (fnd_file.LOG,'pa_project_pub.update_deliverable start');

pa_project_pub.update_deliverable (
  p_api_version                    => l_api_version_number
, p_init_msg_list                  => l_init_msg_list
, p_commit                        => l_commit
, p_debug_mode                    => l_debug_mode
, x_return_status                  => l_return_status
, p_deliverable_name              => 'Upgrade Provider sss test1'--c_project_rec.name  || l_prefix --pa_deliverables_v.element_name
, p_deliverable_short_name        => 'Training sss test1'--c_project_rec.short_name || l_prefix --pa_deliverables_v.element_number
, p_deliverable_type_id            => 10000--10020--v_deliverable_type_id_pgl --pa_deliverables_v.dlvr_type_id
, p_deliverable_owner_id          => 88733--c_project_rec.project_manager_id --pa_deliverables_v.manager_person_id
, p_description                    => 'QTY 70 days of Upgrade Provider Training @ $2,000 ea = $140,000 sss test1'--c_project_rec.description ----pa_deliverables_v.manager_person_id
, p_status_code                    => 'DLVR_IN_PROGRESS'--'DLVR_COMPLETED'--NULL -- c_project_rec.status_code --pa_deliverables_v.deliverable_system_status_code
, p_due_date                      => NULL--'23-MAR-2020'--l_completion_date
, p_completion_date                => NULL--'23-MAR-2020'--NULL
, px_deliverable_id                => l_deliverable_id
, p_pm_deliverable_reference      => l_proj_source_reference
, p_project_id                    => 331512--334827--c_project_rec.project_id --pa_deliverables_v.project_id
, p_action_in_tbl                  => l_action_in
, x_action_out_tbl                => l_action_out
, p_pm_source_code                => l_pm_source_code
, x_msg_count                      => l_msg_count
, x_msg_data                      => l_msg_data
);

commit;

fnd_file.put_line (fnd_file.LOG,'l_return_status:'||l_return_status);
fnd_file.put_line (fnd_file.LOG,'l_deliverable_id:'||l_deliverable_id);

IF l_return_status <> 'S' THEN
l_pgld_error_count :=   l_pgld_error_count + 1;

fnd_msg_pub.get (p_msg_index          => 1
, p_encoded            => 'F'
, p_data                => l_msg_data
, p_msg_index_out      => l_msg_dummy
);

l_msg_data := SUBSTR (l_msg_data, 1, 250);
fnd_file.put_line (fnd_file.LOG,'l_msg_data:'||l_msg_data);
v_msg_data := v_msg_data || g_enter || l_msg_data;
fnd_file.put_line (fnd_file.LOG,'v_msg_data:'||v_msg_data);
ELSE

fnd_file.put_line (fnd_file.LOG,'Deliverable updated successfully for deliverable_id:'||l_deliverable_id);
END IF;

END;

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;

How to Copy files from one directory to other in plsql

1. Create directory SSS_UTL_DIR
CREATE DIRECTORY SSS_UTL_DIR AS '/var/tmp/LR12TST2';

2. check directory in dba_directories
select * from dba_directories
where directory_name in ('SSS_UTL_DIR')

3. run below script to copy the file in the same directory.
DECLARE
v_file VARCHAR2(100):='HR_SSS_03032020064038_Copy.xml';
BEGIN
Utl_File.Fcopy('SSS_UTL_DIR','HR_SSS_03032020064038.xml','SSS_UTL_DIR',v_file);
END;