API'S WITH EXAMPLES

API to Load Values into Value Sets

001

DECLARE

002	----------------------------Local Variables---------------------------

003	l_enabled_flag             VARCHAR2 (2);

004	l_summary_flag             VARCHAR2 (2);

005	l_who_type                 FND_FLEX_LOADER_APIS.WHO_TYPE;

006	l_user_id                  NUMBER                := FND_GLOBAL.USER_ID;

007	l_login_id                 NUMBER                := FND_GLOBAL.LOGIN_ID;

008	l_value_set_name           FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE;

009	l_value_set_value          FND_FLEX_VALUES.FLEX_VALUE%TYPE;

010

BEGIN

011

012	l_value_set_name             :='VALUE_SET_NAME';

013	l_value_set_value            :='VALUE_SET_VALUE';

014	l_enabled_flag               := 'Y';

015	l_summary_flag               := 'N';

016	l_who_type.created_by        := l_user_id;

017	l_who_type.creation_date     := SYSDATE;

018	l_who_type.last_updated_by   := l_user_id;

019	l_who_type.last_update_date  := SYSDATE;

020	l_who_type.last_update_login := l_login_id;

021

022	fnd_flex_loader_apis.up_value_set_value

023	(p_upload_phase               => 'BEGIN',

024	p_upload_mode                => NULL,

025	p_custom_mode                => 'FORCE',

026	p_flex_value_set_name        => l_value_set_name,

027	p_parent_flex_value_low      => NULL,

028	p_flex_value                 => l_value_set_value,

029	p_owner                      => NULL,

030	p_last_update_date           => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),

031	p_enabled_flag               => l_enabled_flag,

032	p_summary_flag               => l_summary_flag,

033	p_start_date_active          => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),

034	p_end_date_active            => NULL,

035	p_parent_flex_value_high     => NULL,

036	p_rollup_flex_value_set_name => NULL,

037	p_rollup_hierarchy_code      => NULL,

038	p_hierarchy_level            => NULL,

039	p_compiled_value_attributes  => NULL,

040	p_value_category             => 'VALUE_SET_NAME',

041	p_attribute1                 => '40912',

042	p_attribute2                 => NULL,

043	p_attribute3                 => NULL,

044	p_attribute4                 => NULL,

045	p_attribute5                 => NULL,

046	p_attribute6                 => NULL,

047	p_attribute7                 => NULL,

048	p_attribute8                 => NULL,

049	p_attribute9                 => NULL,

050	p_attribute10                => NULL,

051	p_attribute11                => NULL,

052	p_attribute12                => NULL,

053	p_attribute13                => NULL,

054	p_attribute14                => NULL,

055	p_attribute15                => NULL,

056	p_attribute16                => NULL,

057	p_attribute17                => NULL,

058	p_attribute18                => NULL,

059	p_attribute19                => NULL,

060	p_attribute20                => NULL,

061	p_attribute21                => NULL,

062	p_attribute22                => NULL,

063	p_attribute23                => NULL,

064	p_attribute24                => NULL,

065	p_attribute25                => NULL,

066	p_attribute26                => NULL,

067	p_attribute27                => NULL,

068	p_attribute28                => NULL,

069	p_attribute29                => NULL,

070	p_attribute30                => NULL,

071	p_attribute31                => NULL,

072	p_attribute32                => NULL,

073	p_attribute33                => NULL,

074	p_attribute34                => NULL,

075	p_attribute35                => NULL,

076	p_attribute36                => NULL,

077	p_attribute37                => NULL,

078	p_attribute38                => NULL,

079	p_attribute39                => NULL,

080	p_attribute40                => NULL,

081	p_attribute41                => NULL,

082	p_attribute42                => NULL,

083	p_attribute43                => NULL,

084	p_attribute44                => NULL,

085	p_attribute45                => NULL,

086	p_attribute46                => NULL,

087	p_attribute47                => NULL,

088	p_attribute48                => NULL,

089	p_attribute49                => NULL,

090	P_ATTRIBUTE50                => NULL,

091	p_flex_value_meaning         => l_value_set_value,

092	p_description                => NULL

093

);

094

COMMIT;

095

096

EXCEPTION

097	WHEN OTHERS

098

THEN

099	DBMS_OUTPUT,PUT_LINE('Error is ' || SUBSTR (SQLERRM, 1, 1000));

100

END;

Deriving Oracle GL Account Code Combination ID’s (CCID’s) through APIs

1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed.
It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.
It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package.
Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.
This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.
The Combination_id output may be NULL if combination is invalid.
Example: (Tested in R12.1.3)

view sourceprint?

01	SET serveroutput ON;

02

DECLARE

03	l_application_short_name VARCHAR2(240);

04	l_key_flex_code          VARCHAR2(240);

05	l_structure_num          NUMBER;

06	l_validation_date        DATE;

07	n_segments               NUMBER;

08	SEGMENTS                 APPS.FND_FLEX_EXT.SEGMENTARRAY;

09	l_combination_id         NUMBER;

10	l_data_set               NUMBER;

11	l_return                 BOOLEAN;

12	l_message                VARCHAR2(240);

13

BEGIN

14	l_application_short_name := 'SQLGL';

15	l_key_flex_code          := 'GL#';

16

17	SELECT id_flex_num

18	INTO l_structure_num

19	FROM apps.fnd_id_flex_structures

20	WHERE ID_FLEX_CODE        = 'GL#'

21	AND ID_FLEX_STRUCTURE_CODE=<ACCOUNTING_FLEXFIELD>;

22

23	l_validation_date        := SYSDATE;

24	n_segments               := 6;

25	segments(1)              := '00101';

26	segments(2)              := '28506';

27	segments(3)              := '00000';

28	segments(4)              := '09063';

29	segments(5)              := '00000';

30	segments(6)              := '00000';

31	l_data_set               := NULL;

32

33	l_return := FND_FLEX_EXT.GET_COMBINATION_ID(

34	application_short_name => l_application_short_name,

35	key_flex_code          => l_key_flex_code,

36	structure_number       => l_structure_num,

37	validation_date        => l_validation_date,

38	n_segments             => n_segments,

39	segments               => segments,

40	combination_id         => l_combination_id,

41	data_set               => l_data_set

42

);

43	l_message:= FND_FLEX_EXT.GET_MESSAGE;

44

45	IF l_return THEN

46	DBMS_OUTPUT.PUT_LINE('l_Return = TRUE');

47	DBMS_OUTPUT.PUT_LINE('COMBINATION_ID = ' || l_combination_id);

48

ELSE

49	DBMS_OUTPUT.PUT_LINE('Error: '||l_message);

50

END IF;

51

END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.
This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).
Various Operations that can be performed are:

• ‘FIND_COMBINATION’ – Combination must already exist.
• ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
• ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
• ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
• ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
• ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.
Example: (Tested in R12.1.3)

view sourceprint?

01	SET serveroutput ON;

02

DECLARE

03	l_segment1   GL_CODE_COMBINATIONS.SEGMENT1%TYPE;

04	l_segment2   GL_CODE_COMBINATIONS.SEGMENT2%TYPE;

05	l_segment3   GL_CODE_COMBINATIONS.SEGMENT3%TYPE;

06	l_segment4   GL_CODE_COMBINATIONS.SEGMENT4%TYPE;

07	l_segment5   GL_CODE_COMBINATIONS.SEGMENT5%TYPE;

08	l_segment6   GL_CODE_COMBINATIONS.SEGMENT6%TYPE;

09	l_valid_combination BOOLEAN;

10	l_cr_combination    BOOLEAN;

11	l_ccid       GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;

12	l_structure_num FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;

13	l_conc_segs GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;

14	p_error_msg1                 VARCHAR2(240);

15	p_error_msg2                 VARCHAR2(240);

16

BEGIN

17	l_segment1  := '00101';

18	l_segment2  := '28506';

19	l_segment3  := '00000';

20	l_segment4  := '14302';

21	l_segment5  := '00455';

22	l_segment6  := '00000';

23	l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;

24

BEGIN

25	SELECT id_flex_num

26	INTO l_structure_num

27	FROM apps.fnd_id_flex_structures

28	WHERE id_flex_code        = 'GL#'

29	AND id_flex_structure_code='EPC_GL_ACCOUNTING_FLEXFIELD';

30

EXCEPTION

31	WHEN OTHERS THEN

32	l_structure_num:=NULL;

33

END;

34	---------------Check if CCID exits with the above Concatenated Segments---------------

35

BEGIN

36	SELECT code_combination_id

37	INTO l_ccid

38	FROM apps.gl_code_combinations_kfv

39	WHERE concatenated_segments = l_conc_segs;

40

EXCEPTION

41	WHEN OTHERS THEN

42	l_ccid:=NULL;

43

END;

44	IF l_ccid IS NOT NULL THEN

45	------------------------The CCID is Available----------------------

46	DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);

47

ELSE

48	DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');

49	------------Validate the New Combination--------------------------

50	l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS

51

(

52	operation => 'CHECK_COMBINATION',

53	appl_short_name => 'SQLGL',

54	key_flex_code => 'GL#',

55	structure_number => L_STRUCTURE_NUM,

56	concat_segments => L_CONC_SEGS

57

);

58	p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

59

60	IF l_valid_combination then

61

62	DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');

63	-------------------Create the New CCID--------------------------

64

65	L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS

66

(

67	operation => 'CREATE_COMBINATION',

68	appl_short_name => 'SQLGL',

69	key_flex_code => 'GL#',

70	structure_number => L_STRUCTURE_NUM,

71	concat_segments => L_CONC_SEGS );

72	p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

73

74	IF l_cr_combination THEN

75	-------------------Fetch the New CCID--------------------------

76	SELECT code_combination_id

77	INTO l_ccid

78	FROM apps.gl_code_combinations_kfv

79	WHERE concatenated_segments = l_conc_segs;

80	DBMS_OUTPUT.PUT_LINE('NEW COMBINATION_ID = ' || l_ccid);

81

ELSE

82	-------------Error in creating a combination-----------------

83	DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);

84

END IF;

85

ELSE

86	--------The segments in the account string are not defined in gl value set----------

87	DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);

88

END IF;

89

END IF;

90

EXCEPTION

91	WHEN OTHERS THEN

92	DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);

93

END;

AP Table Handler APIs

These APIs are handful when you want to do Insert, Update or Delete in some AP Base tables i.e. AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS, AP_INVOICE_PAYMENTS and AP_CHECKS_ALL programmatically for some business requirements (rare cases!).
1] AP_AI_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_INVOICES_ALL table.
• Procedure Update_Row:  Updates a row in AP_INVOICES_ALL table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICES_ALL table. Also subsequently delete rows in the related tables like AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_HOLDS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL.

2] AP_AIL_TABLE_HANDLER_PKG:

• Procedure CHECK_UNIQUE: Check the Uniqueness of a Row.
• Procedure Insert_Row:  Inserts a row in AP_INVOICE_LINES_ALL table.
• Procedure Update_Row:  Updates a row in AP_INVOICE_LINES_ALL table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICE_LINES_ALL table. Also subsequently delete rows in the related tables like AP_INVOICE_DISTRIBUTIONS_ALL.

3] AP_AID_TABLE_HANDLER_PKG:

• Procedure CHECK_UNIQUE: Check the Uniqueness of a Row.
• Procedure Insert_Row:  Inserts a row in AP_INVOICE_DISTRIBUTIONS table.
• Procedure Update_Row:  Updates a row in AP_INVOICE_DISTRIBUTIONS table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICE_DISTRIBUTIONS table.

4] AP_AIP_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_INVOICE_PAYMENTS table.
• Procedure Update_Amounts: Update amounts in AP_INVOICE_PAYMENTS table.

5] AP_AC_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_CHECKS_ALL table.
• Procedure Update_Row:  Updates a row in AP_CHECKS table.
• Procedure Delete_Row:  Deletes a row in AP_CHECKS table.
• Procedure Update_Amounts: Update amounts in AP_CHECKS table.

Filed under APIs, Payables Tagged with AP_AC_TABLE_HANDLER_PKG, AP_AID_TABLE_HANDLER_PKG, AP_AIL_TABLE_HANDLER_PKG, AP_AIP_TABLE_HANDLER_PKG, AP_AI_TABLE_HANDLER_PKG

API to Update Task Information in Oracle Projects

April 9, 2012 2 Comments

Here is one API to update Task Information in Oracle Projects. Here I have used the API to update the Task Manager Information.

view sourceprint?

001

DECLARE

002

003	l_return_status                    VARCHAR(10);

004	l_msg_count                        VARCHAR(240);

005	l_MSG_DATA                         VARCHAR(240);

006	l_rowid                            VARCHAR2(240);

007	task_record                        PA_TASKS%ROWTYPE;

008	task_struc_record                  PA_PROJ_ELEMENTS%ROWTYPE;

009	l_last_updated_by                  NUMBER := FND_GLOBAL.USER_ID;

010	l_last_update_date                 DATE;

011	l_last_update_login                NUMBER := FND_GLOBAL.LOGIN_ID;

012	pt_task_name                       PA_TASKS.TASK_NAME%TYPE;

013	l_task_name                        PA_PROJ_ELEMENTS.NAME%TYPE;

014	l_task_name1                       PA_PROJ_ELEMENTS.NAME%TYPE;

015	l_project_id                       PA_PROJECTS_ALL.PROJECT_ID%TYPE;

016	l_task_id                          PA_TASKS.TASK_ID%TYPE;

017	l_task_manager_person_id           PA_TASKS.TASK_MANAGER_PERSON_ID%TYPE;

018	l_output                           VARCHAR2 (2000);

019	l_msg_dummy                        VARCHAR2 (2000);

020	n                                  NUMBER := 0;

021

022

BEGIN

023

024	l_project_id :=590;

025	l_task_id    :=3355;

026	l_task_manager_person_id :=136263;    --Koch, Dibyajyoti

027

028

BEGIN

029

SELECT *

030	INTO task_record

031	FROM pa_tasks

032	WHERE project_id =l_project_id

033	AND task_id =l_task_id;

034

EXCEPTION

035	WHEN OTHERS THEN

036

NULL;

037

END;

038

039

BEGIN

040

SELECT *

041	INTO task_struc_record

042	FROM pa_proj_elements

043	WHERE PROJECT_ID=l_project_id

044	AND PROJ_ELEMENT_ID=l_task_id;

045

EXCEPTION

046	WHEN OTHERS THEN

047

NULL;

048

END;

049

050

BEGIN

051	SELECT ROWID

052	INTO l_rowid

053	FROM pa_tasks

054	WHERE project_id =l_project_id

055	AND task_id =l_task_id;

056

EXCEPTION

057	WHEN OTHERS THEN

058

NULL;

059

END;

060

061

BEGIN

062	SELECT SYSDATE

063	INTO l_last_update_date

064	FROM DUAL;

065

EXCEPTION

066	WHEN OTHERS THEN

067

NULL;

068

END;

069

070

BEGIN

071	SELECT task_name

072	INTO pt_task_name

073	FROM pa_tasks

074	WHERE task_id = l_task_id;

075

076	SELECT name

077	INTO l_task_name

078	FROM pa_proj_elements

079	WHERE proj_element_id = l_task_id;

080

081	IF pt_task_name = l_task_name THEN

082	l_task_name1 :=l_task_name;

083

ELSE

084	l_task_name1 := pt_task_name;

085

END IF;

086

087

EXCEPTION

088	WHEN OTHERS THEN

089

NULL;

090

END;

091

092	PA_TASKS_PKG.UPDATE_ROW(

093	X_ROWID                        =>l_rowid,

094	X_TASK_ID                      =>task_record.task_id,

095	X_PROJECT_ID                   =>task_record.project_id,

096	X_TASK_NUMBER                  =>task_record.task_number,

097	X_LAST_UPDATE_DATE             =>l_last_update_date, --Updated Value

098	X_LAST_UPDATED_BY              =>l_last_updated_by, --Updated Value

099	X_LAST_UPDATE_LOGIN            =>l_last_update_login,--Updated Value

100	X_Task_Name                    =>task_record.task_name,

101	X_Long_Task_Name               =>task_record.long_task_name,

102	X_TOP_TASK_ID                  =>task_record.top_task_id,

103	X_WBS_LEVEL                    =>task_record.wbs_level,

104	X_READY_TO_BILL_FLAG           =>task_record.ready_to_bill_flag,

105	X_READY_TO_DISTRIBUTE_FLAG     =>task_record.ready_to_distribute_flag,

106	X_PARENT_TASK_ID               =>task_record.parent_task_id,

107	X_DESCRIPTION                  =>task_record.description,

108	X_CARRYING_OUT_ORGANIZATION_ID =>task_record.carrying_out_organization_id,

109	X_SERVICE_TYPE_CODE            =>task_record.service_type_code,

110	X_TASK_MANAGER_PERSON_ID       =>l_task_manager_person_id,--Updated Value

111	X_CHARGEABLE_FLAG              =>task_record.chargeable_flag,

112	X_BILLABLE_FLAG                =>task_record.billable_flag,

113	X_LIMIT_TO_TXN_CONTROLS_FLAG   =>task_record.limit_to_txn_controls_flag,

114	X_START_DATE                   =>task_record.start_date,

115	X_COMPLETION_DATE              =>task_record.completion_date,

116	X_ADDRESS_ID                   =>task_record.address_id,

117	X_LABOR_BILL_RATE_ORG_ID       =>task_record.labor_bill_rate_org_id,

118	X_LABOR_STD_BILL_RATE_SCHDL    =>task_record.labor_std_bill_rate_schdl,

119	X_LABOR_SCHEDULE_FIXED_DATE    =>task_record.labor_schedule_fixed_date,

120	X_LABOR_SCHEDULE_DISCOUNT      =>task_record.labor_schedule_discount,

121	X_NON_LABOR_BILL_RATE_ORG_ID   =>task_record.non_labor_bill_rate_org_id,

122	X_NL_STD_BILL_RATE_SCHDL       =>task_record.non_labor_std_bill_rate_schdl,

123	X_NL_SCHEDULE_FIXED_DATE       =>task_record.non_labor_schedule_fixed_date,

124	X_NON_LABOR_SCHEDULE_DISCOUNT  =>task_record.non_labor_schedule_discount,

125	X_LABOR_COST_MULTIPLIER_NAME   =>task_record.labor_cost_multiplier_name,

126	X_ATTRIBUTE_CATEGORY           =>task_record.attribute_category,

127	X_ATTRIBUTE1                   =>task_record.attribute1,

128	X_ATTRIBUTE2                   =>task_record.attribute2,

129	X_ATTRIBUTE3                   =>task_record.attribute3,

130	X_ATTRIBUTE4                   =>task_record.attribute4,

131	X_ATTRIBUTE5                   =>task_record.attribute5,

132	X_ATTRIBUTE6                   =>task_record.attribute6,

133	X_ATTRIBUTE7                   =>task_record.attribute7,

134	X_ATTRIBUTE8                   =>task_record.attribute8,

135	X_ATTRIBUTE9                   =>task_record.attribute9,

136	X_ATTRIBUTE10                  =>task_record.attribute10,

137	X_COST_IND_RATE_SCH_ID         =>task_record.cost_ind_rate_sch_id,

138	X_REV_IND_RATE_SCH_ID          =>task_record.rev_ind_rate_sch_id,

139	X_INV_IND_RATE_SCH_ID          =>task_record.inv_ind_rate_sch_id,

140	X_COST_IND_SCH_FIXED_DATE      =>task_record.cost_ind_sch_fixed_date,

141	X_REV_IND_SCH_FIXED_DATE       =>task_record.rev_ind_sch_fixed_date,

142	X_INV_IND_SCH_FIXED_DATE       =>task_record.inv_ind_sch_fixed_date,

143	X_LABOR_SCH_TYPE               =>task_record.labor_sch_type,

144	X_NON_LABOR_SCH_TYPE           =>task_record.non_labor_sch_type,

145	X_ALLOW_CROSS_CHARGE_FLAG      =>task_record.allow_cross_charge_flag,

146	X_PROJECT_RATE_DATE            =>task_record.project_rate_date,

147	X_PROJECT_RATE_TYPE            =>task_record.project_rate_type,

148	X_CC_PROCESS_LABOR_FLAG        =>task_record.cc_process_labor_flag,

149	X_LABOR_TP_SCHEDULE_ID         =>task_record.labor_tp_schedule_id,

150	X_LABOR_TP_FIXED_DATE          =>task_record.labor_tp_fixed_date,

151	X_CC_PROCESS_NL_FLAG           =>task_record.cc_process_nl_flag,

152	X_NL_TP_SCHEDULE_ID            =>task_record.nl_tp_schedule_id,

153	X_NL_TP_FIXED_DATE             =>task_record.nl_tp_fixed_date,

154	X_RECEIVE_PROJECT_INVOICE_FLAG =>task_record.receive_project_invoice_flag,

155	X_WORK_TYPE_ID                 =>task_record.work_type_id,

156	X_JOB_BILL_RATE_SCHEDULE_ID    =>task_record.job_bill_rate_schedule_id,

157	X_emp_bill_rate_schedule_id    =>task_record.emp_bill_rate_schedule_id,

158	X_taskfunc_cost_rate_type      =>task_record.taskfunc_cost_rate_type,

159	X_taskfunc_cost_rate_date      =>task_record.taskfunc_cost_rate_date,

160	X_non_lab_std_bill_rt_sch_id   =>task_record.non_lab_std_bill_rt_sch_id,

161	X_labor_disc_reason_code       =>task_record.labor_disc_reason_code,

162	X_non_labor_disc_reason_code   =>task_record.non_labor_disc_reason_code,

163	x_retirement_cost_flag         =>task_record.retirement_cost_flag,

164	x_cint_eligible_flag           =>task_record.cint_eligible_flag,

165	X_CINT_STOP_DATE               =>task_record.cint_stop_date,

166	X_GEN_ETC_SRC_CODE             =>task_record.gen_etc_source_code

167

);

168

169	PA_PROJ_TASK_STRUC_PUB.UPDATE_TASK_STRUCTURE2(

170	p_calling_module                  =>'FORMS',

171	p_task_id                      =>task_record.task_id,

172	p_task_number                 =>task_record.task_number,

173	p_task_name                 =>l_task_name1,

174	P_TASK_DESCRIPTION          =>TASK_RECORD.DESCRIPTION,

175	p_task_manager_id           =>l_task_manager_person_id,--Updated Value

176	p_carrying_out_organization_id =>task_record.carrying_out_organization_id,

177	p_pm_product_code         =>task_record.pm_product_code,

178	p_pm_task_reference       =>task_record.pm_task_reference,

179	p_location_id                  =>task_struc_record.location_id,

180	p_ref_task_id                  =>NULL,

181	p_project_id                   =>task_struc_record.project_id,

182	x_msg_count                    =>l_msg_count,

183	x_msg_data                     =>l_msg_data,

184	x_return_status                =>l_return_status

185

);

186

COMMIT;

187

188	IF l_return_status <> 'S'

189

THEN

190	FOR n IN 1 .. l_msg_count

191

LOOP

192	fnd_msg_pub.get (n, fnd_api.g_false, l_msg_data, l_msg_dummy);

193	l_output := (TO_CHAR (n) || ': ' || l_msg_data);

194	DBMS_OUTPUT.put_line

195	( 'Error: API Error while updating the Task: '

196	|| l_output

197

);

198

COMMIT;

199

END LOOP;

200

ELSE

201	DBMS_OUTPUT.put_line ('Sucessfully Update the task');

202

COMMIT;

203

END IF;

204

EXCEPTION

205	WHEN OTHERS

206

THEN

207	DBMS_OUTPUT.PUT_LINE ('Other Error in Project: ' || SQLERRM);

208

END;

API to add Classification to an Oracle Project

The below API can be used to add a Classification to an Oracle Project.

01

DECLARE

02	l_project_id pa_projects_all.project_id%type            :=NULL;

03	l_class_category pa_project_classes.class_category%type :=NULL;

04	l_class_code pa_project_classes.class_code%type         :=NULL;

05	l_return_status VARCHAR2(20);

06	l_msg_count     NUMBER;

07	l_msg_data      VARCHAR2(240);

08

BEGIN

09	L_PROJECT_ID     := &P_PROJECT_ID;

10	L_CLASS_CATEGORY := &P_CLASS_CATEGORY;

11	l_class_code     := &p_class_code;

12	pa_projects_maint_pub.create_classifications

13	(p_api_version                  => 1.0                   ,

14	p_init_msg_list                 => fnd_api.g_true        ,

15	p_commit                        => fnd_api.g_false       ,

16	p_validate_only                 => fnd_api.g_false        ,

17	p_validation_level              => fnd_api.g_valid_level_full,

18	p_calling_module                => 'SELF_SERVICE'        ,

19	p_debug_mode                    => 'N',

20	p_max_msg_count                 => fnd_api.g_miss_num    ,

21	p_object_id                     => l_project_id,

22	p_object_type                   => 'PA_PROJECTS',

23	p_class_category                => l_class_category       ,

24	p_class_code                    => l_class_code           ,

25	p_code_percentage               => fnd_api.g_miss_num    ,

26	p_attribute_category            => fnd_api.g_miss_char   ,

27	p_attribute1                    => fnd_api.g_miss_char   ,

28	p_attribute2                    => fnd_api.g_miss_char   ,

29	p_attribute3                    => fnd_api.g_miss_char   ,

30	p_attribute4                    => fnd_api.g_miss_char   ,

31	p_attribute5                    => fnd_api.g_miss_char   ,

32	p_attribute6                    => fnd_api.g_miss_char   ,

33	p_attribute7                    => fnd_api.g_miss_char   ,

34	p_attribute8                    => fnd_api.g_miss_char   ,

35	p_attribute9                    => fnd_api.g_miss_char   ,

36	p_attribute10                   => fnd_api.g_miss_char   ,

37	p_attribute11                   => fnd_api.g_miss_char   ,

38	p_attribute12                   => fnd_api.g_miss_char   ,

39	p_attribute13                   => fnd_api.g_miss_char   ,

40	p_attribute14                   => fnd_api.g_miss_char   ,

41	p_attribute15                   => fnd_api.g_miss_char   ,

42	x_return_status                 => l_return_status  ,

43	x_msg_count                     => l_msg_count    ,

44	x_msg_data                      => l_msg_data);

45

COMMIT;

46

47	dbms_output.put_line('Status:'||l_return_status);

48	dbms_output.put_line('Message:'||l_msg_data);

49

END;

API to update and assign Project Roles in an Oracle Project

Oracle has provided a seeded package called PA_PROJECT_PARTIES_PUB to create, update or delete a project party (or Key member) in an oracle project. From front end, the navigation is Project Billing Super User (or related responsibility) > Projects > Find Projects > Open > Options > Key Members. The records in the form are displayed through a view (PA_PROJECT_PLAYERS) and the base table is PA_PROJECT_PARTIES.
PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY:

view sourceprint?

01

DECLARE

02

03	l_project_id              PA_PROJECT_PARTIES.PROJECT_ID%TYPE  :=NULL;

04	l_project_role            VARCHAR2(240) :=NULL;

05	l_resource_name           PER_ALL_PEOPLE_F.FULL_NAME%TYPE :=NULL;

06	l_start_date_active       DATE :=NULL;

07	l_end_date_active         DATE :=NULL;

08	l_project_role_id         pa_project_role_types.PROJECT_ROLE_ID%TYPE :=NULL;

09	l_project_role_type       pa_project_role_types.PROJECT_ROLE_TYPE%TYPE :=NULL;

10	l_resource_source_id      PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE :=NULL;

11	l_project_party_id        PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE :=NULL;

12	l_object_id               PA_PROJECT_PARTIES.OBJECT_ID%TYPE :=NULL;

13	l_resource_id             PA_PROJECT_PARTIES.RESOURCE_ID%TYPE  :=NULL;

14	l_record_version_number   pa_project_parties.record_version_number%type  :=null;

15	l_project_end_date        DATE;

16	l_return_status           VARCHAR2(20) :=NULL;

17	l_assignment_id           NUMBER :=NULL;

18	l_wf_type                 VARCHAR2(240) :=NULL;

19	l_wf_item_type            VARCHAR2(240) :=NULL;

20	l_wf_process              VARCHAR2(240) :=NULL;

21	l_msg_count               NUMBER :=NULL;

22	l_msg_data                VARCHAR2(240) :=NULL;

23

24

BEGIN

25

26	---Input Parameters----

27	l_project_id        := '7033';

28	l_project_role      := 'Project Manager';

29	l_resource_name     := 'Koch, Dibyajyoti';

30	l_start_date_active := '24-NOV-2011';

31	l_end_date_active   := '24-NOV-2012';

32

33	SELECT PROJECT_ROLE_ID,

34	PROJECT_ROLE_TYPE

35	INTO  l_project_role_id,

36	l_project_role_type

37	FROM PA_PROJECT_ROLE_TYPES

38	WHERE UPPER(MEANING) =UPPER(l_project_role);

39

40	SELECT DISTINCT PERSON_ID

41	INTO l_resource_source_id

42	FROM PER_ALL_PEOPLE_F

43	WHERE UPPER(FULL_NAME) =UPPER(l_resource_name);

44

45	SELECT PROJECT_PARTY_ID,

46	OBJECT_ID,

47	RESOURCE_ID,

48	RECORD_VERSION_NUMBER

49	INTO l_project_party_id,

50	l_object_id,

51	l_resource_id,

52	l_record_version_number

53	FROM PA_PROJECT_PARTIES

54	WHERE PROJECT_ID= l_project_id

55	AND PROJECT_ROLE_ID= l_project_role_id

56	AND RESOURCE_SOURCE_ID= l_resource_source_id;

57

58	l_project_end_date:= pa_project_dates_utils.get_project_finish_date(l_project_id);

59

60	PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY( P_API_VERSION  => 1.0,

61	P_INIT_MSG_LIST         => FND_API.G_TRUE,

62	P_COMMIT                => FND_API.G_FALSE,

63	P_VALIDATE_ONLY         => FND_API.G_FALSE,

64	P_VALIDATION_LEVEL      => FND_API.G_VALID_LEVEL_FULL,

65	P_DEBUG_MODE            => 'N',

66	P_OBJECT_ID             => l_object_id,

67	P_OBJECT_TYPE           => 'PA_PROJECTS',

68	P_PROJECT_ROLE_ID       => l_project_role_id,

69	P_PROJECT_ROLE_TYPE     => l_project_role_type,

70	P_RESOURCE_TYPE_ID      => 101, --EMPLOYEE

71	P_RESOURCE_SOURCE_ID    => l_resource_source_id,

72	P_RESOURCE_NAME         => l_resource_name,

73	P_RESOURCE_ID           => l_resource_id,

74	P_START_DATE_ACTIVE     => l_start_date_active,

75	P_SCHEDULED_FLAG        => 'N',

76	P_RECORD_VERSION_NUMBER => l_record_version_number,

77	P_CALLING_MODULE        => FND_API.G_MISS_CHAR,

78	P_PROJECT_ID            => l_project_id,

79	P_PROJECT_END_DATE      => l_project_end_date,

80	P_PROJECT_PARTY_ID      => l_project_party_id,

81	P_ASSIGNMENT_ID         => null,

82	P_ASSIGN_RECORD_VERSION_NUMBER =>l_record_version_number+1,

83	P_MGR_VALIDATION_TYPE   => 'FORM',

84	P_END_DATE_ACTIVE       => l_end_date_active,

85	X_ASSIGNMENT_ID         => l_assignment_id,

86	X_WF_TYPE               => l_wf_type,

87	X_WF_ITEM_TYPE          => l_wf_item_type,

88	X_WF_PROCESS            => l_wf_process,

89	X_RETURN_STATUS         => l_return_status,

90	X_MSG_COUNT             => l_msg_count,

91	x_msg_data              => l_msg_data);

92

COMMIT;

93	DBMS_OUTPUT.PUT_LINE('Status:'||l_return_status);

94	DBMS_OUTPUT.PUT_LINE('Message:'||l_msg_data);

95

EXCEPTION

96	when OTHERS then

97	DBMS_OUTPUT.PUT_LINE('Try Again!!');

98

END;

PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY:

view sourceprint?

01

DECLARE

02

03	l_project_id              PA_PROJECT_PARTIES.PROJECT_ID%TYPE  :=NULL;

04	l_project_role            VARCHAR2(240) :=NULL;

05	l_resource_name           PER_ALL_PEOPLE_F.FULL_NAME%TYPE :=NULL;

06	l_start_date_active       DATE :=NULL;

07	l_end_date_active         DATE :=NULL;

08	l_project_role_id         pa_project_role_types.PROJECT_ROLE_ID%TYPE :=NULL;

09	l_project_role_type       pa_project_role_types.PROJECT_ROLE_TYPE%TYPE :=NULL;

10	l_resource_source_id      PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE :=NULL;

11	l_project_party_id        PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE :=NULL;

12	l_object_id               PA_PROJECT_PARTIES.OBJECT_ID%TYPE :=NULL;

13	l_resource_id             PA_PROJECT_PARTIES.RESOURCE_ID%TYPE  :=NULL;

14	l_record_version_number   pa_project_parties.record_version_number%type  :=null;

15	l_project_end_date        DATE;

16	l_return_status           VARCHAR2(20) :=NULL;

17	l_assignment_id           NUMBER :=NULL;

18	l_wf_type                 VARCHAR2(240) :=NULL;

19	l_wf_item_type            VARCHAR2(240) :=NULL;

20	l_wf_process              VARCHAR2(240) :=NULL;

21	l_msg_count               NUMBER :=NULL;

22	l_msg_data                VARCHAR2(240) :=NULL;

23

24

BEGIN

25

26	---Input Parameters----

27	l_project_id        := '7033';

28	l_project_role      := 'Project Accountant';

29	l_resource_name     := 'Koch, Dibyajyoti';

30	l_start_date_active := '24-NOV-2011';

31	l_end_date_active   := '24-NOV-2012';

32

33	SELECT PROJECT_ROLE_ID,

34	PROJECT_ROLE_TYPE

35	INTO l_project_role_id,

36	l_project_role_type

37	FROM PA_PROJECT_ROLE_TYPES

38	WHERE UPPER(MEANING) =UPPER(l_project_role);

39

40	SELECT DISTINCT PERSON_ID

41	INTO l_resource_source_id

42	FROM PER_ALL_PEOPLE_F

43	WHERE UPPER(FULL_NAME) =UPPER(l_resource_name);

44

45	l_project_end_date:= pa_project_dates_utils.get_project_finish_date(l_project_id);

46

47	PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY( P_API_VERSION    => 1.0,

48	P_INIT_MSG_LIST                   => FND_API.G_TRUE,

49	P_COMMIT                          => FND_API.G_FALSE,

50	P_VALIDATE_ONLY                   => FND_API.G_FALSE,

51	P_VALIDATION_LEVEL                => FND_API.G_VALID_LEVEL_FULL,

52	P_DEBUG_MODE                      => 'N',

53	P_OBJECT_ID                       => l_project_id,

54	P_OBJECT_TYPE                     => 'PA_PROJECTS',

55	P_PROJECT_ROLE_ID                 => l_project_role_id,

56	P_PROJECT_ROLE_TYPE               => l_project_role_type,

57	P_RESOURCE_TYPE_ID                => 101, --EMPLOYEE

58	P_RESOURCE_SOURCE_ID              => l_resource_source_id,

59	P_RESOURCE_NAME                   => l_resource_name,

60	P_START_DATE_ACTIVE               => l_start_date_active,

61	P_SCHEDULED_FLAG                  => 'N',

62	P_CALLING_MODULE                  => NULL,

63	P_PROJECT_ID                      => l_project_id,

64	P_PROJECT_END_DATE                => l_project_end_date,

65	P_MGR_VALIDATION_TYPE             => 'FORM',

66	P_END_DATE_ACTIVE                 => l_end_date_active,

67	X_PROJECT_PARTY_ID                => l_project_party_id,

68	X_RESOURCE_ID                     => l_resource_id,

69	X_ASSIGNMENT_ID                   => l_assignment_id,

70	X_WF_TYPE                         => l_wf_type,

71	X_WF_ITEM_TYPE                    => l_wf_item_type,

72	X_WF_PROCESS                      => l_wf_process,

73	X_RETURN_STATUS                   => l_return_status,

74	X_MSG_COUNT                       => l_msg_count,

75	X_MSG_DATA                        => l_msg_data

76

);

77

COMMIT;

78	DBMS_OUTPUT.PUT_LINE('Status:'||l_return_status);

79	DBMS_OUTPUT.PUT_LINE('Message:'||l_msg_data);

80

EXCEPTION

81	when OTHERS then

82	DBMS_OUTPUT.PUT_LINE('Try Again!!');

83

end;

Utility APIs for Concurrent Processing

FND_CONCURRENT.GET_REQUEST_STATUS

This API Returns the Status of a concurrent request. It also returns the completion text if the request is already completed. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

view sourceprint?

1	function get_request_status(request_id     IN OUT NOCOPY number,

2	appl_shortname IN varchar2 default NULL,

3	program        IN varchar2 default NULL,

4	phase      OUT NOCOPY varchar2,

5	status     OUT NOCOPY varchar2,

6	dev_phase  OUT NOCOPY varchar2,

7	dev_status OUT NOCOPY varchar2,

8	message    OUT NOCOPY varchar2) return boolean;

The parameters are:

• REQUEST_ID: Request ID of the program to be checked.
• APPL_SHORTNAME: Short name of the application associated with the program. The default is NULL.
• PROGRAM: Short name of the concurrent program. The default is NULL.
• PHASE: Request phase.
• STATUS: Request status.
• DEV_PHASE: Request phase as a string constant.
• DEV_STATUS: Request status as a string constant.
• MESSAGE: Request completion message.

FND_CONCURRENT.WAIT_FOR_REQUEST

This API waits for the request completion, then returns the request phase/status and completion message to the caller. It goes to sleep between checks for the request completion. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

view sourceprint?

1	function wait_for_request(request_id IN number default NULL,

2	interval   IN  number default 60,

3	max_wait   IN  number default 0,

4	phase      OUT NOCOPY varchar2,

5	status     OUT NOCOPY varchar2,

6	dev_phase  OUT NOCOPY varchar2,

7	dev_status OUT NOCOPY varchar2,

8	message    OUT NOCOPY varchar2) return  boolean;

The parameters are:

• REQUEST_ID: Request ID of the request to wait on. The default is NULL.
• INTERVAL: Number of seconds to wait between checks. The default is 60 seconds.
• MAX_WAIT: Maximum number of seconds to wait for the request completion. The default is 00 seconds.
• PHASE: User-friendly Request phase.
• STATUS: User-friendly Request status.
• DEV_PHASE: Request phase as a constant string.
• DEV_STATUS: Request status as a constant string.
• MESSAGE: Request completion message.

There are few other useful apis too.

• FND_CONCURRENT.SET_COMPLETION_STATUS: Called from a concurrent request to set its completion status and message.
• FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS: Returns the print options for a concurrent request.
• FND_CONCURRENT.GET_SUB_REQUESTS: Get all sub-requests for a given request id. For each sub-request it provides request_id, phase,status, developer phase , developer status and completion text.
• FND_CONCURRENT.Cancel_Request: It cancels a given Concurrent Request.

API to Assign Item to an Organization in Oracle Inventory

EGO_ITEM_PUB package provides functionality for maintaining items, item revisions, etc. We can use ASSIGN_ITEM_TO_ORG procedure to assign one item to an organization.
The procedure definition is:

view sourceprint?

01	PROCEDURE Assign_Item_To_Org(

02	p_api_version             IN      NUMBER

03	,p_init_msg_list           IN      VARCHAR2        DEFAULT  G_FALSE

04	,p_commit                  IN      VARCHAR2        DEFAULT  G_FALSE

05	,p_Inventory_Item_Id       IN      NUMBER          DEFAULT  G_MISS_NUM

06	,p_Item_Number             IN      VARCHAR2        DEFAULT  G_MISS_CHAR

07	,p_Organization_Id         IN      NUMBER          DEFAULT  G_MISS_NUM

08	,p_Organization_Code       IN      VARCHAR2        DEFAULT  G_MISS_CHAR

09	,p_Primary_Uom_Code        IN      VARCHAR2        DEFAULT  G_MISS_CHAR

10	,x_return_status           OUT NOCOPY  VARCHAR2

11	,x_msg_count               OUT NOCOPY  NUMBER);

The parameters are:

• P_API_VERSION – A decimal number indicating major and minor revisions to the API. Pass 1.0 unless otherwise indicated in the API parameter list.
• P_INIT_MSG_LIST – A one-character flag indicating whether to initialize the FND_MSG_PUB package’s message stack at the beginning of API processing (and thus remove any messages that may exist on the stack from prior processing in the same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
• P_COMMIT – A one-character flag indicating whether to commit work at the end of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
• P_INVENTORY_ITEM_ID – Inventory Item Id of the Item
• P_ITEM_NUMBER – Segment1 of the Item
• P_ORGANIZATION_ID – Organization Id of the Organization to whom Item must be assigned
• P_ORGANIZATION_CODE – 3 character Organization Code of the Organization to whom Item must be assigned
• P_PRIMARY_UOM_CODE – Primary Unit of Measure of the item.
• X_RETURN_STATUS – A one-character code indicating whether any errors occurred during processing (in which case error messages will be present on the FND_MSG_PUB package’s message stack). Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.
• X_MSG_COUNT – An integer indicating the number of messages on the FND_MSG_PUB package’s message stack at the end of API processing.

Sample Code: (Tested in R12.1.3)

view sourceprint?

01

DECLARE

02	g_user_id             fnd_user.user_id%TYPE :=NULL;

03	l_appl_id             fnd_application.application_id%TYPE;

04	l_resp_id             fnd_responsibility_tl.responsibility_id%TYPE;

05	l_api_version         NUMBER := 1.0;

06	l_init_msg_list       VARCHAR2(2) := fnd_api.g_false;

07	l_commit              VARCHAR2(2) := FND_API.G_FALSE;

08	x_message_list        error_handler.error_tbl_type;

09	x_return_status       VARCHAR2(2);

10	x_msg_count           NUMBER := 0;

11

BEGIN

12	SELECT fa.application_id

13	INTO l_appl_id

14	FROM fnd_application fa

15	WHERE fa.application_short_name = 'INV';

16

17	SELECT fr.responsibility_id

18	INTO l_resp_id

19	FROM fnd_application fa, fnd_responsibility_tl fr

20	WHERE fa.application_short_name = 'INV'

21	AND fa.application_id = fr.application_id

22	AND UPPER (fr.responsibility_name) = 'INVENTORY';

23

24	fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

25

26	EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(

27	P_API_VERSION          => l_api_version

28	,  P_INIT_MSG_LIST        => l_init_msg_list

29	,  P_COMMIT               => l_commit

30	,  P_INVENTORY_ITEM_ID    => 1003

31	,  p_item_number          => 000000000001035

32	,  p_organization_id      => 11047

33	,  P_ORGANIZATION_CODE    => 'DXN'

34	,  P_PRIMARY_UOM_CODE     => 'EA'

35	,  X_RETURN_STATUS        => x_return_status

36	,  X_MSG_COUNT            => x_msg_count

37

);

38	DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);

39	IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN

40	DBMS_OUTPUT.PUT_LINE('Error Messages :');

41	Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);

42	FOR j IN 1..x_message_list.COUNT LOOP

43	DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);

44

END LOOP;

45

END IF;

46

EXCEPTION

47	WHEN OTHERS THEN

48	dbms_output.put_line('Exception Occured :');

49	DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);

50

END;

Oracle Project Foundation APIs

This article gives a brief description of the APIs that you can use to integrate project data from an external system with Oracle Projects. The procedures discussed below are located in the public API package PA_PROJECT_PUB.

Project Procedures:
1] CREATE_PROJECT
CREATE_PROJECT is a PL/SQL procedure that creates a project in Oracle Projects using a template or an existing project.
The Parameters:

Name	Description
P_API_VERSION_NUMBER	API standard version number
P_COMMIT	API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST	API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT	API standard count of error messages
P_MSG_DATA	API standard error message
P_RETURN_STATUS	API standard Return of the API success/failure/unexpected error)
P_WORKFLOW_STARTED	Shows if a workflow has been started (Y or N)
P_PM_PRODUCT_CODE	Identifier of the external systems from which the project was imported
P_PROJECT_IN	Input project details
P_PROJECT_OUT	Output project details
P_KEY_MEMBERS	The identification code for the role that the members have on the project
P_CLASS_CATEGORIES	Identification code for the categories by which the project is classified
P_TASKS_IN	Input task details of the project
P_TASKS_OUT	Output task details of the project
P_ORG_ROLES	Identifier for organization roles for project
P_STRUCTURE_IN	Identifier of structure data
P_EXT_ATTR_TBL_IN	Identifier of external attributes

2] DELETE_PROJECT
DELETE_PROJECT is a PL/SQL procedure used to delete a project and its tasks from Oracle Projects.
The Parameters:

Name	Description
P_API_VERSION_NUMBER	API standard version number
P_COMMIT	API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST	API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT	API standard count of error messages
P_MSG_DATA	API standard error message
P_RETURN_STATUS	API standard Return of the API success/failure/unexpected error)
P_PM_PRODUCT_CODE	Identifier of the external systems from which the project was imported
P_PM_PROJECT_REFERENCE	The reference code that uniquely identifies the project in the external system
P_PA_PROJECT_ID	The reference code that uniquely identifies the project in Oracle Projects

3] UPDATE_PROJECT
UPDATE_PROJECT is a PL/SQL procedure that updates project and task information from your external system to Oracle Projects to reflect changes you have made in the external system. UPDATE_PROJECT uses composite datatypes.
The Parameters:

Name	Description
P_API_VERSION_NUMBER	API standard version number
P_COMMIT	API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST	API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT	API standard count of error messages
P_MSG_DATA	API standard error message
P_RETURN_STATUS	API standard Return of the API success/failure/unexpected error)
P_WORKFLOW_STARTED	Shows if a workflow has been started (Y or N)
P_PM_PRODUCT_CODE	Identifier of the external systems from which the project was imported
P_PROJECT_IN	Input project details
P_PROJECT_OUT	Output project details
P_KEY_MEMBERS	The identification code for the role that the members have on the project
P_CLASS_CATEGORIES	Identification code for the categories by which the project is classified
P_TASKS_IN	Input task details of the project
P_TASKS_OUT	Output task details of the project
P_ORG_ROLES	Identifier for organization roles for project
P_STRUCTURE_IN	Identifier of structure data
P_PASS_ENTIRE_STRUCTURE	Flag indicating whether to pass entire structure
P_EXT_ATTR_TBL_IN	Identifier of external attributes.

Load-Execute-Fetch Procedures:
The following is the list of API’s for Load-Execute-Fetch and should be executed in the order of sequence.

• INIT_PROJECT
• LOAD_PROJECT
• LOAD_TASK
• LOAD_CLASS_CATEGORY
• LOAD_KEY_MEMBER
• EXECUTE_CREATE_PROJECT/EXECUTE_UPDATE_PROJECT
• FETCH_TASK
• CLEAR_PROJECT

Check Procedures:

CHECK_DELETE_PROJECT_OK	This API is used to determine if you can delete a project.
CHECK_CHANGE_PROJECT_ORG_OK	This API is used to determine if you can change the CARRYING_OUT_ORGANIZATION_ID field for a particular project or task.
CHECK_CHANGE_PARENT_OK	This API is used to determine if you can move a task from one parent task to another.
CHECK_UNIQUE_PROJECT_REFERENCE	This API is used to determine if a new or changed project reference(PM_PROJECT_REFERENCE) is unique
CHECK_ADD_SUBTASK_OK	This API is used to determine if a subtask can be added to a parent task.
CHECK_DELETE_TASK_OK	This API is used to determine if you can delete a task.
CHECK_TASK_NUMBER_CHANGE_OK	This API is used to determine if you can change a tasks number.
CHECK_UNIQUE_TASK_NUMBER	This API is used to determine if a new or changed task number is unique within a project.
CHECK_UNIQUE_TASK_REFERENCE	This API is used to determine if a new or changed task reference (PM_TASK_REFERENCE) is unique

You can refer Oracle Projects APIs, Client Extensions, and Open Interfaces for the record and table types used. Go to Oracle Integration Repository for more details in the above procedures.
Thanks..Have a nice day!

Item Category Creation APIs

There are few APIs in INV_ITEM_CATEGORY_PUB package related to item category. This article will follow a category flexfield structure. Please refer the below post for more detail.

How to Create Category and Category Set in Oracle Inventory?

INV_ITEM_CATEGORY_PUB.Create_Category

view sourceprint?

01

DECLARE

02	l_category_rec    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;

03	l_return_status   VARCHAR2(80);

04	l_error_code      NUMBER;

05	l_msg_count       NUMBER;

06	l_msg_data        VARCHAR2(80);

07	l_out_category_id NUMBER;

08

BEGIN

09	l_category_rec.segment1 := 'RED';

10

11	SELECT f.ID_FLEX_NUM

12	INTO l_category_rec.structure_id

13	FROM FND_ID_FLEX_STRUCTURES f

14	WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS';

15

16	l_category_rec.description := 'Red';

17

18	INV_ITEM_CATEGORY_PUB.Create_Category

19

(

20	p_api_version   => 1.0,

21	p_init_msg_list => FND_API.G_FALSE,

22	p_commit        => FND_API.G_TRUE,

23	x_return_status => l_return_status,

24	x_errorcode     => l_error_code,

25	x_msg_count     => l_msg_count,

26	x_msg_data      => l_msg_data,

27	p_category_rec  => l_category_rec,

28	x_category_id   => l_out_category_id

29

);

30	IF l_return_status = fnd_api.g_ret_sts_success THEN

31

COMMIT;

32	DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id);

33

ELSE

34	DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code);

35

ROLLBACK;

36

END IF;

37

END ;

INV_ITEM_CATEGORY_PUB. Delete_Category

view sourceprint?

01

DECLARE

02	l_return_status VARCHAR2(80);

03	l_error_code    NUMBER;

04	l_msg_count     NUMBER;

05	l_msg_data      VARCHAR2(80);

06	l_category_id   NUMBER;

07

BEGIN

08	SELECT mcb.CATEGORY_ID

09	INTO l_category_id

10	FROM mtl_categories_b mcb

11	WHERE mcb.SEGMENT1='RED'

12	AND mcb.STRUCTURE_ID =

13	(SELECT mcs_b.STRUCTURE_ID

14	FROM mtl_category_sets_b mcs_b

15	WHERE mcs_b.CATEGORY_SET_ID =

16	(SELECT mcs_tl.CATEGORY_SET_ID

17	FROM mtl_category_sets_tl mcs_tl

18	WHERE CATEGORY_SET_NAME ='INV_COLORS_SET'

19

)

20

);

21

22	INV_ITEM_CATEGORY_PUB.Delete_Category

23

(

24	p_api_version     => 1.0,

25	p_init_msg_list   => FND_API.G_FALSE,

26	p_commit          => FND_API.G_TRUE,

27	x_return_status   => l_return_status,

28	x_errorcode       => l_error_code,

29	x_msg_count       => l_msg_count,

30	x_msg_data        => l_msg_data,

31	p_category_id     => l_category_id);

32

33	IF l_return_status = fnd_api.g_ret_sts_success THEN

34

COMMIT;

35	DBMS_OUTPUT.put_line ('Deletion of Item Category is Successful : '||l_category_id);

36

ELSE

37	DBMS_OUTPUT.put_line ('Deletion of Item Category Failed with the error :'||l_error_code);

38

ROLLBACK;

39

END IF;

40

END ;

INV_ITEM_CATEGORY_PUB.Update_Category_Description
Updates the category description.

view sourceprint?

01

DECLARE

02	l_return_status VARCHAR2(80);

03	l_error_code    NUMBER;

04	l_msg_count     NUMBER;

05	l_msg_data      VARCHAR2(80);

06	l_category_id   NUMBER;

07	l_description   VARCHAR2(80);

08

BEGIN

09	select mcb.CATEGORY_ID into l_category_id

10	from mtl_categories_b mcb

11	where mcb.SEGMENT1='BLACK'

12	and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID

13	from mtl_category_sets_b mcs_b

14	where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID

15	from mtl_category_sets_tl mcs_tl

16	where CATEGORY_SET_NAME ='INV_COLORS_SET'));

17

18	l_description := 'new black color';

19

20	INV_ITEM_CATEGORY_PUB.Update_Category_Description (

21	p_api_version     => 1.0,

22	p_init_msg_list   => FND_API.G_FALSE,

23	p_commit          => FND_API.G_TRUE,

24	x_return_status   => l_return_status,

25	x_errorcode       => l_error_code,

26	x_msg_count       => l_msg_count,

27	x_msg_data        => l_msg_data,

28	p_category_id     => l_category_id,

29	p_description     => l_description);

30

31	IF l_return_status = fnd_api.g_ret_sts_success THEN

32

COMMIT;

33	DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id);

34

ELSE

35	DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code);

36

ROLLBACK;

37

END IF;

38

END ;

Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.
INV_ITEM_CATEGORY_PUB.Create_Valid_Category
Create a record in mtl_category_set_valid_cats.

view sourceprint?

01

DECLARE

02	l_return_status   VARCHAR2(80);

03	l_error_code      NUMBER;

04	l_msg_count       NUMBER;

05	l_msg_data        VARCHAR2(80);

06	l_category_set_id NUMBER;

07	l_category_id     NUMBER;

08

BEGIN

09	select mcs_tl.CATEGORY_SET_ID into l_category_set_id

10	from mtl_category_sets_tl mcs_tl

11	where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

12

13	select mcb.CATEGORY_ID into l_category_id

14	from mtl_categories_b mcb

15	where mcb.SEGMENT1='RED'

16	and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID

17	from mtl_category_sets_b mcs_b

18	where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID

19	from mtl_category_sets_tl mcs_tl

20	where CATEGORY_SET_NAME ='INV_COLORS_SET'));

21

22	INV_ITEM_CATEGORY_PUB.Create_Valid_Category (

23	p_api_version        => 1.0,

24	p_init_msg_list      => FND_API.G_FALSE,

25	p_commit             => FND_API.G_TRUE,

26	x_return_status      => l_return_status,

27	x_errorcode          => l_error_code,

28	x_msg_count          => l_msg_count,

29	x_msg_data           => l_msg_data,

30	p_category_set_id    => l_category_set_id,

31	p_category_id        => l_category_id,

32	p_parent_category_id => NULL );

33

34	IF l_return_status = fnd_api.g_ret_sts_success THEN

35

COMMIT;

36	DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id);

37

ELSE

38	DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code);

39

ROLLBACK;

40

END IF;

41

END ;

INV_ITEM_CATEGORY_PUB.Delete_Valid_Category
Delete the record from mtl_category_set_valid_cats.

view sourceprint?

01

DECLARE

02	l_return_status    VARCHAR2(80);

03	l_error_code       NUMBER;

04	l_msg_count        NUMBER;

05	l_msg_data         VARCHAR2(80);

06	l_category_set_id  NUMBER;

07	l_category_id      NUMBER;

08

BEGIN

09	select mcs_tl.CATEGORY_SET_ID into l_category_set_id

10	from mtl_category_sets_tl mcs_tl

11	where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

12

13	select mcb.CATEGORY_ID into l_category_id

14	from mtl_categories_b mcb

15	where mcb.SEGMENT1='RED'

16	and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID

17	from mtl_category_sets_b mcs_b

18	where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID

19	from mtl_category_sets_tl mcs_tl

20	where CATEGORY_SET_NAME ='INV_COLORS_SET'));

21

22	INV_ITEM_CATEGORY_PUB.Delete_Valid_Category (

23	p_api_version      => 1.0,

24	p_init_msg_list    => FND_API.G_FALSE,

25	p_commit           => FND_API.G_TRUE,

26	x_return_status    => l_return_status,

27	x_errorcode        => l_error_code,

28	x_msg_count        => l_msg_count,

29	x_msg_data         => l_msg_data,

30	p_category_set_id  => l_category_set_id,

31	p_category_id      => l_category_id);

32

33	IF l_return_status = fnd_api.g_ret_sts_success THEN

34

COMMIT;

35	DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id);

36

ELSE

37	DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code);

38

ROLLBACK;

39

END IF;

40

END ;

The above scripts are tested in R12.1.3